MySQL表索引

Question

I have mysql tabel called 'users'. There are two obvious fields: 'user' and 'pass'.

My authentication query is like this:

"SELECT pass FROM users WHERE user = ? AND pass = ?"

I'd tought indexing could speed up the query. Should I index only 'user' field or also 'pass' field?

Also is comparing passwords with sql query faster than with PHP string compare.

Answer 1

First of all, you shouldn't be storing passwords in plain text. Store password hashes instead.

Second, index on either user or pass will help. But if you create a compound index on (user, pass), it will become a covering index for this query. This means that the query can be served directly from index, without consulting data files.