1

I am currently looking at how to most efficiently delete rows from a table in bulk. The database I'm using is MySQL with innodb.

The table I want to delete from can be any size, but will usually be ~10gb, and has a primary key (int) and indexed hash. This hash is non-unique, and I do drop it before proceeding with the deletes. The table has no other indexes.

I have a second table containing only the id's I wish to delete. The number of id's to delete can be anything from none, to everything.

The problem is I can't decide on a strategy on how to delete these rows quickly. I am aware of 2, which serve opposite sides of the extreme.

Option 1: The standard DELETE FROM table_data WHERE id IN table_todelete. This is good if there are very few deletions.

Option 2: Create a new table. Insert rows from table_data where id NOT in table_todelete. Drop table_data and rename the newly created table. This is obviously better when there are a large number of deletes.

I do not know enough about the performance implications of select, insert, delete, to know how fast they are compared to each other. My question is, since my data isn't skewed towards always having many or few deletes, which approach is better in terms of performance? Is there some sort of theoretical threshold at which I can swap between the two approaches? If there is a third option I might've not considered, please let me know.

Thanks.

Improve this question
4
  • From preformacne viewpoint you will probably need to benchmark it yourself, if the treshold is 30%, 50%, or 90%. But Option 2 has some bigger implications about needed locking to keep data consistent. You need to forbid inserts and updates to it for the whole time so your copied data are valid and that may be problem for your application if it takes few seconds or eve few minutes and the app cannot write anything. I personally would suggest opt1 with some reasonable limit (say 1000-100000 rows - again based on benchmark) and periodical runs until everything is deleted.
    – jkavalik
    May 25, 2015 at 11:01
  • Thanks for the feedback. Due to the nature of the application, insert and update locks shouldn't be an issue. My feeling is that the threshold will be a combination of percentage and minimum values. I wanted to avoid empirical data as that would require a lot of test cases to eventually narrow down on something reliable.
    – Kevin
    May 25, 2015 at 12:52
  • Is the goal to finish the deletion in minimal time? Or to have minimal impact on other queries?
    – Rick James
    Jun 7, 2015 at 3:34
  • The goal is to finish in minimal time
    – Kevin
    Jun 7, 2015 at 10:20

1 Answer 1

Reset to default
1

You pretty much answered your own question. Creating a new table will be more efficient when you want to delete a large portion of the table. Just counting the number of records to modify will give you a general idea of the impact. However, you also want to account for time spent updating indexes. If you are deleting a lot of records, you may be concerned about slack space in the table, meaning that each block is only partially filled. Considering all of this together, if you need to delete more than about 30% of a table, then creating a new table would be faster.

On the down-side, creating a second copy of the table will use more disk space, even if only temporarily. It will disrupt database replication, so it is generally not a good option when replication is involved.

If the goal is to minimize impact on the database, then I recommend pt-archiver, from the Percona toolkit. It deletes records in batches and keeps commits to a reasonable size. It's a good option if you want to automate your purge to run on a daily basis.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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