Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Suppose in a table there are 2 columns: login and password

login is indexed as unique index, password not indexed.

  1. query: SELECT * FROM mytable WHERE login = 'Jhon'
  2. query: SELECT * FROM mytable WHERE login = 'Jhon' AND password = '123'

After finding first row, will mysql stop or continue searching?

What will mysql do in first case 1. and 2. ?

share|improve this question
add comment

2 Answers 2

If the constraint on login is unique... any serious sql database vendor will stop searching at the first and only possible occurence met.

In the second case, it will stop at the first and only possible occurence, and returns the record if the password is '123', nothing if not.

share|improve this answer
add comment

AFAIK, no, SQL will keep searching through the DB entries looking for matches to the query until it has reached the end of the table.
EDIT: Not entirely sure on this, so don't rely on it.
EDIT AGAIN: Thinking on it some, I have a feeling the WHERE clause may be applied before the SELECT statement, so it filters the data you are querying first. So, no, it doesn't "keep looking". The result-set is, I believe, built as the query runs - it'll take all the rows from the database/table you're querying, apply the WHERE clauses to filter it to valid records only and then apply the SELECT statement (in this case SELECT * will return the whole set. But you could've asked it for just the TOP n records or something similar.)

And I'm afraid I don't get what you mean in your second question - are you asking the difference in the results that'll be returned? If so, the first query will return any results whose login is "Jhon", whereas the second will only return records whose login is "Jhon" and whosee password is "123"

As you have said that login is unique, I understand that these will most likely be the same (assuming there's an entry matching the second query). However, if you had a record whose name was "Jhon" and whose password was "password", that record would be returned by the first query, not the second.
EDIT: In this instance, the second query would return an empty set (assuming there are no other "Jhon"s in the DB with password "123"

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.