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?
Since the query will only return, in any given moment, one record; then the addition of LIMIT 1 will not increase your execution time.
I'm not sure which algorithm MySQL uses in order to perform LIMIT 1 requests. However ironic, it may use comparison expressions or iterators to return only 1 record – in which case, the addition of LIMIT 1 may actually delay execution time by several instructions.
In most of the cases where I query on unique fields I still use the LIMIT 1. I mostly do this because I want to ensure that no matter what anyone does with the database/tables my query will never return or manipulate more than one row.
Typically this should also cover the case of a dynamically malformed query to mess up the complete database (and no - I'm not talking about SQL injection).
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 |
No difference, no need. It appears to be optimized out in this case (only searching against the primary key).
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.
If the field has a unique index on it, I can't see this improving execution time at all - it's a stretch even by micro-optimization standards.
However, I can see it potentially masking other problems. Say you add LIMIT 1 to these queries, and then somehow the field upon which you're querying loses its unique index and the db gets multiple rows with the same value. This code will keep chugging along happily - I'd think you might want to fail fast instead, to become aware of (and fix) the bigger underlying problem.
In my own db interface code, I have a method queryOneRow() that runs some SQL and throws an exception if it gets more than one row back. It makes the most sense to me to handle this explicitly at the application layer rather than defensively in SQL.