Would LIMIT 0,1 speed up a SELECT on a Primary Key

Question

Does anyone know if there is any speed difference (obviously for tables that are sizable enough) between these two queries:

SELECT field FROM table WHERE primary_key = "a particular value"

or:

SELECT field FROM table WHERE primary_key = "a particular value" LIMIT 0,1

I should note that the primary_key field is actually a primary key.

Now LIMIT 0,1 does help when a query would otherwise carry on to find other matches. I'm assuming though that when a primary key is involved it should automatically stop due to it's uniqueness. So my guess is that there wouldn't be any gain by adding it.

Anyone come across this before? I don't have a sufficiently large dataset lying around to test with at the moment. I'd also assume that the same answer would apply for any field that has been set to be UNIQUE.

Answer 1

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

Answer 2

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.