sql - Is there any point using MySQL "LIMIT 1" when querying on indexed/unique field?

Question

For example, I'm querying on a field I know will be unique and is indexed such as a primary key. Hence I know this query will only return 1 row (even without the LIMIT 1)

SELECT * FROM tablename WHERE tablename.id=123 LIMIT 1

or only update 1 row

UPDATE tablename SET somefield='somevalue' WHERE tablename.id=123 LIMIT 1

Would adding the LIMIT 1 improve query execution time if the field is indexed?

question from:https://stackoverflow.com/questions/3848390/is-there-any-point-using-mysql-limit-1-when-querying-on-indexed-unique-field

Answer 1

Is there any point using MySQL “LIMIT 1” when querying on primary key/unique field?

It is not good practice to use LIMIT 1 when querying with filter criteria that is against either a primary key or unique constraint. A primary key, or unique constraint, means there is only one row/record in the table with that value, only one row/record will ever be returned. It's contradictory to have LIMIT 1 on a primary key/unique field--someone maintaining the code later could mistake the importance & second guess your code.

But the ultimate indicator is the explain plan:

explain SELECT t.name FROM USERS t WHERE t.userid = 4

...returns:

id  | select_type | table   | type  | possible_keys  | key      | key_len  |  ref  |  rows  |  Extra
-----------------------------------------------------------------------------------------------------
1   | SIMPLE      | users   | const | PRIMARY        | PRIMARY  | 4        | const | 1      |

...and:

explain SELECT t.name FROM USERS t WHERE t.userid = 4 LIMIT 1

...returns:

id  | select_type | table   | type  | possible_keys  | key      | key_len  |  ref  |  rows  |  Extra
-----------------------------------------------------------------------------------------------------
1   | SIMPLE      | users   | const | PRIMARY        | PRIMARY  | 4        | const | 1      |

Conclusion

No difference, no need. It appears to be optimized out in this case (only searching against the primary key).

What about an indexed field?

An indexed field doesn't guarantee uniqueness of the value being filtered, there could be more than one occurrence. So LIMIT 1 would make sense, assuming you want to return one row.