26 databases too many?

[The Little Guy]

Is it necessary to have 26+ databases for just user names and logins?

 

each database would be ordered by email:

A... - in one database

B... - in another database

C... - in a third database

and this would continue through Z

 

The problem I see with this is when you go to a users profile page, with info about them, comments, etc. How would I know what database to pull from? so if their id is 12355 aka a url like: http://mysite.com/profile.php?id=12355 how would you know what database to pull that from, and when they register how would you give them their own unique id?

 

I was told this should be done to reduce the CPU search amount on a database... is it even necessary? This would be if you had 1 million plus users.

[cooldude832]

First off I think you mean 26 tables and not database

secondly why

 

Mysql tables are designed for a broad range of loads to be placed on them.

You should try and limit your tables and when you see a need for a table add it.

Don't seperate by first character because you think its a good idea.  SQL is great at handling large loads

[The Little Guy]

That is what I thought.

 

The person who told me was just the messenger of another person... he couldn't remember if it was tables or databases. I assume it is tables too, but I still don't think that 26 tables would be required for this job.

[cooldude832]

My user systems (one has 3500 users) is only 4-5 tables

 

User  Info (passwords, Usernames, Email, Last Login, Contact Info)

User Profile (Image, Bio blob etc)

User Activities (Page views, Login times etc.)

The rest are site specifc for needs

 

I have no issues running thousands of memebers and don't see any coming

[Yesideez]

One way to make things a little easier (and more compact) is try not to duplicate data.

 

For example, if a table has a field for storing something like "Where did you hear about us" you could have a table set aside purely for that. List a few sample places (eg. Friend, Web Link, Magazine, Search Engine etc.) then you can reference this by using a numerical field in for example the users table.

 

If you have an option for "Other - please specify" then when a user enters some text saying where else they heard about your site you can then add that into your where from table and point to that new entry in your users table.

[roopurt18]

If you have millions of active users, then you are probably making a fair amount of money through advertising.  If that's the case, you can probably afford some sort of load balancing solution that will be much more effective, as well as not creating all sorts of complicated programming problems.

[wizzbangca]

Have to agree to the load balancing solution.  The typical login tables I create are mixed with customer information.  I have never had problems with 1 login table, let along 1 customer information table with login information inside the table.  I haven't gone above a few hundred thousand users.  If I got into the million + users, I would go with self hosting and load balancing.

 

26 tables for logins?  never.  That would create a programming nightmare, not to mention an information management nightmare.  If you don't understand databases, as the impression I am getting with the question, you definitely need to look up designing databases.  make sure normalization is covered.

 

[The Little Guy]

Oh I understand databases very well.

 

Currently we have 9 tables and one database.

 

# comments

# events

# eventStatus

# friends

# images

# mail

# profileComments

# profileInfo

# users

 

all the information in each is unique for each user. There will be duplicates if 2 users enter the same info, but other than that it is 100% unique.

 

276 KB overhead... until I optimized

 

I should probably index email/password pairs... Ill do that now.