Have Geek Brain

Sql userid + name + profile optimize question

Most likely I will need to make a lot of userid-> username queries. So I thought, instead of a table like below

userid int PK
username text
userpass_salthash int
user_someopt1 int
user_sig text

so that it crashes like below

//table1
userid int PK
username text

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

I would do this because I suspect a less complex table (I can also make names no more than 32 bytes if I like) faster to search, and also less data as a bonus. But I know I could be wrong, so which version should I be doing and what are the reasons related to optimization?

+1


a source to share


3 answers


You have to do the first option (single table) for normalization and performance.

Performance:

  • If you put the index on (UserId, Username)

    , you will have the coverage index, so you no longer need to go to the table to get the username anyway.
  • If you put your clustered index on UserId

    , you end up with a clustered index lookup, which ends up in the row data anyway.


Normalization:

  • The second option allows the user to exist on table1 but not on table2. Since you probably don't want a passwordless user (who can't log in), I would think it's broken.

My suggestion would be a clustered index for the UserId. If you need a clustered index somewhere else, the coverage index will be almost as good.

+5


a source


I agree that for a table that is narrow, one table is by far the best choice.



One more thing to add is the text data type (if you are using MS SQL Server) is terribly wide. nvarchar (200) should be more than wide enough. Use LOB data with discretion.

+1


a source


Please do not worry about optimizing this search if you are not sure what it should be. Please remember the Optimization Club rules.

  • The first rule of the Optimization Club is not optimization.
  • The second rule of the Optimization Club is not optimization without measurement.
  • If your application is faster than the underlying transport protocol, the optimization is complete.
  • One factor at a time.
  • No market measures, no market charts.
  • Testing will continue for as long as it should be.
  • If this is your first night at the Optimization Club, you need to write a test case.
+1


a source






More articles:


All Articles