Topic: MySQL design question (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=28581" title="Pages that link to Topic: MySQL design question (Page 1 of 1)" rel="nofollow" >Topic: MySQL design question <span class="small">(Page 1 of 1)</span>\

 
bitdamaged
Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

IP logged posted posted 10-26-2006 21:16 Edit Quote

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 ]:.

terrestrialhost
Nervous Wreck (II) Inmate

From: Cleveland, OH
Insane since: Jan 2006

IP logged posted posted 10-26-2006 23:41 Edit Quote

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.

DL-44
Lunatic (VI) Inmate

From: under the bed
Insane since: Feb 2000

IP logged posted posted 10-27-2006 01:53 Edit Quote

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?

Moon Shadow
Paranoid (IV) Inmate

From: Paris, France
Insane since: Jan 2003

IP logged posted posted 10-27-2006 04:39 Edit Quote

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)

H][RO
Paranoid (IV) Inmate

From: Australia
Insane since: Oct 2002

IP logged posted posted 11-22-2006 02:02 Edit Quote

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



Post Reply
 
Your User Name:
Your Password:
Login Options: Remember Me On This Computer
 
Your Text:
Loading...
Options: Show Signature
Enable Slimies
Enable Linkwords

« BackwardsOnwards »

Show Forum Drop Down Menu