sql userid + name + profile optimize question

Question

Chances are i will need to do a lot of userid->username lookups. So i was thinking, instead of having a table like the below

userid int PK
username text
userpass_salthash int
user_someopt1 int
user_sig text

to have it broken up as the below

//table1
userid int PK
username text

//table2
userid int //fk
userpass_salthash int
user_someopt1 int
user_sig text

reasoning i would do this is because i suspect a less complex table (i can also make names no longer then 32bytes if i like) is faster for lookups along with less data as a bonus. But i know i could be wrong so, which version should i do and what reasons beside optimization?

Answer 1

You should do the first option (single table) for both normalization and performance.

Performance:

• If you put an index on (UserId, Username), you'll have a covering index - so you won't ever need to go to the table to get the Username anyway.
• If you put your clustered index on UserId, you'll get a clustered index seek - which will end up at the row data anyway.

Normalization:

• Your second option allows for a user to exist in table1, but not table2. Since you likely don't want a user without a password (that can't login), I'd consider that broken.

My suggestion would be a clustered index on UserId. If you need the clustered index somewhere else, a covering index would be almost as good.

Answer 2

I agree that for a table that narrow, a single table is almost certainly the best bet.

One more thing to add - a text datatype (if you're using MS SQL Server) is awfully wide. nvarchar(200) should be more than wide enough. Use LOB data with discretion.

Answer 3

Please don't bother worrying about optimizing that lookup unless you are sure it needs to be. Please remember the rules of Optmization Club.

1. The first rule of Optimization Club is, you do not Optimize.
2. The second rule of Optimization Club is, you do not Optimize without measuring.
3. If your app is running faster than the underlying transport protocol, the optimization is over.
4. One factor at a time.
5. No marketroids, no marketroid schedules.
6. Testing will go on as long as it has to.
7. If this is your first night at Optimization Club, you have to write a test case.