Would LIMIT 0,1 speed up a SELECT on a Primary Key?
So after reading the information provided by Hammerite (Thanks for that) running explain
on both queries produces:
- id = 1
- select_type = SIMPLE
- table = table
- type = CONST
- possible_keys = PRIMARY
- key = PRIMARY
- key_len = 767
- ref = const
- rows = 1
- Extra =
Which identifies both queries as CONST
, which the link defines as:
const
The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.
So assuming I understand both queries correctly, they would be treated the same - which is what I had expected.
The primary keys will be searched as an index which is pretty fast.
But Putting limit to your queries is part of best practices.
On any more complex queries it will, with Joins, Group_by and order by or even single searches, it allow sql to stop, after your limits record row_count is found, eg: 1
If your to use order by, it will allow sql to start the search appropriately from the ordered by column and stop after that 1 record is found, even if other records would be found within other specified columns or conditions eg:
SELECT Primary_keycoll,field2 FROM table WHERE Primary_keycoll = "value1" or field2 = 'value1' order by field2 limit 0,1
maybe Primary_keycoll is unique but others eg: field2 might not, limiting to 1 , allow mysql to get the first and stop, else it will go trough the whole specified columns or resources to assure other columns dont fulfill the query.
Using limit at the end of your query is good practice for security reasons, it will block some SQL injection trick. as long that your limiters, are properly sanitizes.
http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html
LIMIT 0 will quickly returns an empty set.