3

When deleting, should I use LIMIT, or not (MySQL INNODB tables)

  • Each customer has a unique hash.
  • Each customer can have one or more records in a single table.

If I know in advance a customer can have up to 100 records maximum, and my DELETE query has LIMIT 100, then I presume once 100 records were deleted, the query will end without going to end of table.

However, if my customer only has 50records, and my DELETE query has LIMIT 100, the delete will continue until end of table is found since it will spend effort chasing the 51st record which does not exist.

Thus... If I do not use LIMIT does MySQL INNODB know in advance that after having deleted 50 records it need not continue further?

The answer can either create or eliminate performance issues...

3

The limit is only going to help with exactly 100 so it is probably not worth the overhead.

If you put an index on the hash then it should know to stop (and where to start).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.