0

i'm running a social network site and i currently have most main user data (real name, username, email, password, gender, etc) in a user table. Design-wise, would there be an performance improvement in moving the username+password to a separate table ?

I'm using mysql.

MrSmith42
  • 9,961
  • 6
  • 38
  • 49
Sherif Buzz
  • 1,218
  • 5
  • 21
  • 38

1 Answers1

0

Authentication queries which only need username and password will run slightly faster, due to the smaller size of the table.

However, if you create a composite index on (username, password_hash), it will be even faster, since the queries like this:

SELECT  password_hash
FROM    users
WHERE   username = 'myusername'

will only need the index to run (it will show using index in the plan).

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • No need to index the password_hash since the username is most likely unique. The query with always either return 0 or 1 rows. – cherouvim Nov 09 '10 at 13:15
  • The purpose of the password_hash in the index is not to make it unique but to make it faster because as Quassnoi states there is no need to go to the table data after the row has been found. – Peter Hahndorf Nov 09 '10 at 13:26
  • Wherever the username and password lives, there is no need to index both fields since the username is probably unique. A query for `user=? and pass=?` will return very quickly using the unique index of username no matter what the password is. – cherouvim Nov 09 '10 at 13:29
  • @cherouvim, I think your missing the point. A table access and index access will always be slower (albeit probably not noticable) then only a index access. – Lieven Keersmaekers Nov 09 '10 at 13:34
  • Yes you are right. It'll be a bit faster but only worth the index data size in case you've got say thousands of logins per second. – cherouvim Nov 09 '10 at 14:30