Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Thanks for the replies, I appreciate it. –  Lee Hinde Dec 10 '11 at 23:21
add comment

3 Answers

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

share|improve this answer
1  
I like DELETE QUICK for MyISAM (specified in your link) because it does what OPTION 2 in my answer does. +1 !!! –  RolandoMySQLDBA Nov 29 '11 at 22:02
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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