Re: Deleting from a big table

[Øystein Grøvlen]

Hi Davide,

MySQL 5.6 significantly improved performance of IN-subqueries, but unfortunately these optimizations are not done for single-table UPDATE/DELETE statements. However, the optimizations are used for multi-table UPDATE/DELETE statements. Hence, I think that the following should give better performance if you are using MySQL 5.6:

DELETE BigTable FROM BigTable
WHERE pk IN
(SELECT pk FROM AnotherBigTable WHERE externalKey='abc');

The point is to use multi-table DELETE syntax for this single-table DELETE. Note that you must be using MySQL 5.6 or newer for this to improve performance.

I hope this helps,

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway