32

When is it appropriate to add LIMIT 1 at the end of the query in MySQL. I normally add it in DELETE but I've seen it being used with INSERT a and even UPDATE. Is it an overkill or a good practice?

santa
  • 12,234
  • 49
  • 155
  • 255

5 Answers5

38
 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...

reformed
  • 4,505
  • 11
  • 62
  • 88
bobflux
  • 11,123
  • 3
  • 27
  • 27
23

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.

Rich Bradshaw
  • 71,795
  • 44
  • 182
  • 241
  • Not today, but I'm pretty sure that in the past I have optimised queries that were complex by doing this. – Rich Bradshaw May 08 '11 at 08:17
  • I know the post is old but rich makes a valid point - once you reach the limit - the requests stop. and also you are ensuring only Limit number is affected. – Ken Sep 15 '16 at 15:27
2

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.

Shawn
  • 21
  • 1
  • A table without a unique key is a surgeon without a scalpel. A table without a unique key is an excel spreadsheet, and, well.. actually, even excel spreadsheets are stored with row numbers. Never create a table without a unique key. At least add an auto increment. otherwise, you will eventually go back and add a unique key, and at such time, you will have to update a lot of application code. –  Apr 08 '20 at 18:18
2

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.

Mikecito
  • 2,053
  • 11
  • 17
1

Assume you are writing Forgot Password code (in PHP):

UPDATE
    `users`
SET
    `pass` = PASSWORD('{$new_salted_pass}')
WHERE
    `account_id` = '{$account_id}'
LIMIT 1

If your software is a Software as a Service (SaaS),

and multiple organizations use your software,

and account_id-organization_id fields together are a unique id,

and the account_id is auto-generated in the code like so:

JONES0001
JONES0002
JONES0003
...

and the software engineer forgets to add AND organization_id = '{$organization_id}' to the query's WHERE condition,

and the code passes QA when testing the Forgot Password functionality in QA environment,

and the code is then pushed to production,

then the LIMIT 1 can limit the damage done when two organizations' user's share the same account_id.