design for speed?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jbkielis
Forum Newbie
Posts: 6
Joined: Thu Aug 04, 2005 4:24 pm

design for speed?

Post by jbkielis »

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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

two tables will, after a long time, be slower than one.

Learn how to normalize data, and you can help speed things and keep them modular.
jbkielis
Forum Newbie
Posts: 6
Joined: Thu Aug 04, 2005 4:24 pm

Post by jbkielis »

What do you mean by normalizing data?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

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.
Post Reply