Let's say you have a database table for user information that has about 20 fields that describe information about the user. Now, it is easy to keep all this information in 1 table and quite easy to query it. However, is it beneficial to split this up into 2 tables. The first containing the bare minimums about the user such as the username and password. The second containing all the information about the user.
Does this speed up queries by a noticeable amount.
Also, let's say your user base starts getting quite large (>500000 users). Is it wise to start splitting up your databases and/or tables? For example, store users with last names A-J in one database (or table) and users with last names K-Z in another database (or table). Does something like that help enough to have to go through the hassle of implementing the procedure?
I guess I'm looking for some general design methodologies.
Thanks very much for all your help.
Jonathan
design for speed?
Moderator: General Moderators
Personally, I would split the tables vertically. (Ie have a "narrow" users table and a "wide" user_details table)
Just about every page in a web-application needs to hit the narrow table. You want to keep it lean so it will stay in memory on the server to the greatest degree possible. A lot of the "profile" type stuff that often gets tacked onto the users table is seldom retrieved or needed. The user table is also almost always getting joined to lots various tables for permission checks, histories, etc, and so keeping it narrow helps a lot.
Yes, this creates a 1:1 table mapping between users and user_details. Yes in older version of MySQL this can be a real pain. (Lack of proper ON UPDATE/ON DELETE behavoir). Normalization won't advocate for or against this, thought 6NF almost addresses it.
If your database supports "clustering" or "partitioning" of a table, then the horizontal decomposition into A-M,N-Z can be accomplished without too much pain, but I wouldn't do it manually.
Just about every page in a web-application needs to hit the narrow table. You want to keep it lean so it will stay in memory on the server to the greatest degree possible. A lot of the "profile" type stuff that often gets tacked onto the users table is seldom retrieved or needed. The user table is also almost always getting joined to lots various tables for permission checks, histories, etc, and so keeping it narrow helps a lot.
Yes, this creates a 1:1 table mapping between users and user_details. Yes in older version of MySQL this can be a real pain. (Lack of proper ON UPDATE/ON DELETE behavoir). Normalization won't advocate for or against this, thought 6NF almost addresses it.
If your database supports "clustering" or "partitioning" of a table, then the horizontal decomposition into A-M,N-Z can be accomplished without too much pain, but I wouldn't do it manually.