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

user_188365 0
Viewed 6269

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?

limit indexing mysql sql performance
5 Answers

Short answer: If you are searching against indexed unique field: there is no diffrence.

However note, that: If you are searching against NOT unique field: you will benefit from adding LIMIT 1 to query. Even if you have index on that field.

For example if you don't have unique index on email field

SELECT * FROM users WHERE email="user@example.com" LIMIT 1

will be faster than

SELECT * FROM users WHERE email="user@example.com"

even though both queries will return one record.

It's because MySQL will stop searching for more records, once it finds one record.

user_56250870
Related