LIMIT 1 on SELECT query using PK

Databases
1

If I have a query like

SELECT field1, field2 
FROM table 
WHERE id = 10 
LIMIT 1

and id is a primary key, is the LIMIT 1 useful?

In other words, when searching a primary key index, does mysql automatically stop the search when it finds the first matching record even when you don’t use a LIMIT clause?

Dave

2

Thanks, I won’t bother using it then. As Guido suggested, I did try BENCHMARK a few times on a SELECT query both with and without LIMIT 1, and the execution times were very similar.

Dave

3

But, being id a primary key, ‘all matching rows’ would still be only 1 :slight_smile:

I guess LIMIT would be useless in this case, but I don’t know. You could always run a test, and see the difference between with and without LIMIT 1.

4

Without the limit clause, mysql would return all matching rows since it would not know you only wanted a single row.

5

It knows from the fact you are referencing it using the primary key that you only want the one - the only effect the LIMIT would have is to make the code less portable if you ever needed to switch to a different database.