Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Keysle
Starting Member
15 Posts |
 Posted - 2010-09-02 : 16:44:21
|
| I plan on having millions of users on my site.Should I keep their general info (password,name,id,email) stored in one table?Right now I have it progged so that it will put the user in a table that has has the first three letters of the users name.I have an email+id table and a username+id that work like this.With alphanumeric+underscore user names and email addresses there will be 104118 tables storing both email and usernames that are attached to id's.I need the emails on a separate table because a user can log in via name OR EMAIL. In order to find that email I need a way to find which table that email is stored in i take the first 3 letters (or 1 or 2 if there are less) and search for the email_index_<3 letters> table and then find the name in that.I'm sorry if this questions sounds stupid, but I talked to a fellow coworker (admittedly he didn't have much experience) but he said it would take more than 3 seconds to find a user if all the users were stored in one huge table.What do pro-league guys do like Google, yahoo, twitter and Facebook?They use something like MySQL or something like it right?I'm using MySQLhttp://Keysle.com |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Keysle
Starting Member
15 Posts |
Posted - 2010-09-02 : 17:01:49
|
| O_OA million users is a medium sized table?Honestly, (and I may be too optimistic) I was thinking the growth of my service would expand to 500,000,000 users.I may be too optimistic but i need to be able to handle such growth.Also I will have a tagging system that will involved words and each word's association to other words, that will reach well over 100,000,000 considering all of the languages that will be used (and all the made up words people make)I hate to noob-tail on you but can you demonstrate proper indexing against improper indexing? Is this to say I Should alphabetize my table order every time a user is added? Is there a way to store different orders for a table (because i want to give my users the option to login by email, and i'm sure that searching process could be enhanced by having two orders)Is my triple letter division method just as fast as storing it in one table? Is there a downside to this method?http://Keysle.com |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-09-02 : 17:36:56
|
| A large table is one with billions of rows in it.Even with a half billion rows, you should still put it into one table, as long as it is properly normalized. You can partition the table if needed for performance reasons. Properly indexes your table is based upon the queries, the data, and the table design. You'll need to post this information for us to help you with the specifics.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Keysle
Starting Member
15 Posts |
Posted - 2010-09-02 : 18:19:01
|
| My general user info table contains (name, password, email, gender, date of birth)So far I've just been adding more users, onto the bottom of the table. also,Is my triple letter division method just as fast as storing it in one table? Is there a downside to this method?http://Keysle.com/* I am going to be on here a lot until I get my project done */ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-02 : 19:22:00
|
| I believe by "Triple Letter Division" OP means manually building a B-Tree of tables. I've listened to this argument before and it is ill-conceived (in my opinion) except in the very largest of applications (think DNA/Genome applications).Having 104,000+ tables is a hugely bad idea. Don't try to reinvent the index. Properly normalized as Tara indicated above, even vewry large tables will perform just fine.You're using mysql, so I'm not going to be able to give you solid advice on implementation (this is a MS SQL SERVER forum), but the design rules won't change.In SQL Server, there are indexed views, which essentially materialize your view. DB2 has a similar concept. Those may or may not be necessary in your case. You can look at a non-clustered index as table containing the fields covered in the index. So you could index Email address (a must if you're going to perform lookups against it) and you could index LEFT(email, 3) if there is some valid reason to do so.I will say this, and please don't be offended, if you're creating an application that will have 500,000,000 users, you need to get the design right from the get-go. And by this, I mean, that if you're asking these types of questions, you may well need to hire a database architect and or DBA -- even if on a consulting basis. If you ever really do reach 100s of millions of users, you will need a team of DBAs. |
|
|
|
|
|
|
|
|
How should I store lists of my users?