2

Which is optimal query for check whether the username ans password present in MySql database.

1)SELECT * FROM login_details WHERE username='username' AND password='password'
2)SELECT count(*)FROM login_details WHERE username='username' AND password='password'
3)SELECT count(Username)FROM login_details WHERE username='username' AND password='password'
4)SELECT 1 FROM login_details WHERE username='username' AND password='password'

Thanks

Sajith
  • 1,738
  • 6
  • 25
  • 41
  • 1
    In my experience, unless you are dealing with a HUGE number of records - hundreds of millions - the indexing scheme is far more important than the difference between the queries you suggest. Also, eggyal is right about the storing of hashed passwords! – Neville Kuyt Nov 27 '12 at 09:49

1 Answers1

11

Hopefully 'password' is actually a salted hash of the user's password, and you're passing both literals from your application as parameters to a prepared statement.

Assuming that you have an index on (username, password), the "optimum" would be:

SELECT EXISTS (
  SELECT * FROM login_details WHERE username = ? AND password = ?
)

This way, MySQL stops searching once it encounters a single result.

Community
  • 1
  • 1
eggyal
  • 113,121
  • 18
  • 188
  • 221
  • Thanks for answer. In this case which is best the inner query? Any performance difference between Select * , Select count(*) or Select 1 in this inner query? – Sajith Nov 27 '12 at 10:07
  • [Traditionally, an `EXISTS` subquery starts with `SELECT *`, but it could begin with `SELECT 5` or `SELECT column1` or anything at all. MySQL ignores the `SELECT` list in such a subquery, so it makes no difference.](http://dev.mysql.com/doc/en/exists-and-not-exists-subqueries.html) – eggyal Nov 27 '12 at 10:21