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
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.