Menu
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?
500
The LIMIT clause is used to specify the number of records to return. The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
This LIMIT clause would return 3 records in the result set with an offset of 1. What this means is that the SELECT statement would skip the first record that would normally be returned and instead return the second, third, and fourth records.
LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements). To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter.
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...
104
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
