Using LIMIT 1 in MySQL

Well, if you use EXPLAIN you'll see that it speeds things up, as once it's found one result, it stops.

It's also a failsafe – if you know your insert of update should only ever affect one row, by specifying it, you are guaranteeing that it won't ever go wrong and mess up multiple rows.


 INSERT INTO .. VALUES () LIMIT 1

Doesn't exist. Hopefully you know how many VALUES() you put in there!

 INSERT INTO .. SELECT ... LIMIT 1

Does exist and is pretty useful, and off topic since the LIMIT is on the SELECT.

 DELETE ... LIMIT 1
 UPDATE ... LIMIT 1

Extremely rarely useful. Either you know your database enough to be certain that your WHERE matches a UNIQUE condition, or you don't, in which case you should spend a little more time looking at your database and learning SQL.

But ...

 UPDATE jobs SET owner=me WHERE owner IS NULL ORDER BY job_submit_time LIMIT 1

Can be extremely useful! This makes a near-lockless job queue, where you can come and take a job from the queue without any waiting, locking, or conflict resolution. Quite excellent.

 DELETE FROM cache ORDER BY last_update_time LIMIT N

Cache takes too much space ? Purge the N oldest rows...


I can't think of an instance where something that speeds up a query and makes it more secure at the same time could be called overkill. I'll put my vote in the good practice column.


Extremely rarely useful. Either you know your database enough to be certain > that your WHERE matches a UNIQUE condition, or you don't, in which case you > should spend a little more time looking at your database and learning SQL

There are instances where a limit 1 is REALLY useful. For example, a table without a unique key. If we have five of the exact same, then limit one will work.

Tags:

Mysql