Topic awaiting preservation: MySQL design question

I have a tendency to design a user table in mysql as two tables

users:
id
user_name
password_hash

user_info:
user_id <- users.id
first_name
last_name
email
AIM
...


There's a 1:1 relationship between users and user_info fields. I do this to try to speed up logons etc because depending on how much stuff I'm storing in user_info that table can get huge. The downside being it's a bit more work to implement the two tables.

So My question is this worth it? Do I see a real speed bonus with this design or am I over complicating things?



.:[ Never resist a perfect moment ]:.

Don't bother seperating it. Instead, just create keys:
Primary keys, unique keys, and just plain index keys.

That'll speed up your search. Seperating it will slow you down overall when you have to request from different tables.

I am clearly no expert on these issues, but as far as I understand, it makes no difference whatsoever how many fields are in your table - if you are only calling for the user_id, user_name, and user_pass, aren't those the only fields that will make a difference in your query speed?

Yep DL you are right.

However,

"First, indexes speed up retrievals but slow down inserts and deletes, as well as updates of values in indexed columns. That is, indexes slow down most operations that involve writing. This occurs because writing a record requires writing not only the data row, it requires changes to any indexes as well. The more indexes a table has, the more changes need to be made, and the greater the average performance degradation."

http://www.informit.com/articles/article.asp?p=377652&rl=1

Therefore, bitdamaged it all depends on the use you make of your tables... If they're really large, and if you need to write often in them, I don't think it would be useful, in terms of speed, to merge them.

----
If wishes were fishes, we'd all cast nets.



(Edited by Moon Shadow on 10-27-2006 04:41)

The only reason I separate things like user data is for things like profiles, where you might be adding/removing keys (which would otherwise be columns). When the data remains consistent and is to be expected you might as well put it in the first table.


Table1:
users:
id
user_name
password_hash
first_name
last_name
email
AIM

Table2:
user_profile:
user_id <- users.id
profile_key (favourite_color)
profile_data (red)



etc..