What is the best approach to run a DELETE query on 92K of 4 million rows?

Question

Options that have occurred to me:

1. One big delete statement. For example:

   delete from x where (y = 'x' and x = 'y') or (y = 'a' and x = 'b')
   

2. Same as above, but chunk in smaller pieces, 10k at a time?

3. Create a stored procedure so all I'm sending are the the two query values.

Answer 1

92K out of 4M is still only 2.3% of your records. I'd suggest your first option, just throw what you want deleted at the database and let the database sort it out (if you give it to the database all at once, chances are it'll do it more efficiently than if you parcel it out). The only time I'd recommend chunking it is if your application is time-critical and you're worried about slowing it down (in which case the other solution is to do it off-hours).

If you were taking 20-30% or more of the records out of the table, I'd recommend dropping indexes, doing the delete, and recreating indexes, followed by an OPTIMIZE TABLE, but for 2% of the records in the table the index rebuild overhead isn't worth it (it's faster just to keep the indexes up-to-date while deleting the records).

That said, after you've done the delete, running OPTIMIZE TABLE might not be a bad idea - don't do that during peak hours though.

Answer 2

I would always use DELETE QUICK instead of just DELETE, as it does less work with the indexes during the delete, and then at a later stage when you get a chance to optimize the table, the index leaves that have been left behind get gathered up.

I'd certainly recommend reading the documentation on DELETE QUICK to see if it will help.

http://dev.mysql.com/doc/refman/5.5/en/delete.html

Answer 3

You want to make sure that your plan is Seeking to find the records of interest. So get it running nicely as a SELECT first with appropriate indexes.

If the locking involved in doing the DELETEs is a pain, then do it in batches. But if your query is scanning the table, then using batches will just end up in scanning the table multiple times.