Menu
I have to delete about 10K rows from a table that has more than 100 million rows based on some criteria. When I execute the query, it takes about 5 minutes. I ran an explain plan (the delete query converted to select * since MySQL does not support explain delete) and found that MySQL uses the wrong index.
My question is: is there any way to tell MySQL which index to use during delete? If not, what ca I do? Select to temp table then delete from temp table?
760
Index hints give the optimizer information about how to choose indexes during query processing. Index hints apply only to SELECT and UPDATE statements. Index hints are specified following a table name.
The answer is yes, Indexes do help with the DELETE statement. Let us see a simple example of the SELECT command and its execution plan. Here you will be able to see the query using the index in the execution plan.
MySQL DELETE statement doesn't use index although the same SELECT query does. Save this question.
There is index hint syntax. //ETA: sadly, not for deletes
ETA:
Have you tried running ANALYZE TABLE $mytable?
If that doesn't pay off, I'm thinking you have 2 choices: Drop the offending index before the delete and recreate it after. Or JOIN your delete table to another table on the desired index which should ensure that the desired index is used.
74
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
