MySQL, delete and index hint

mysql query-optimization sql-delete sql-execution-plan
Manuel Darveau picture Manuel Darveau · May 27, 2010 · Viewed 11.5k times · Source

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?

Answer

dnagirl picture dnagirl · May 27, 2010

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.

Related questions

mysql select from n last rows

I have a table with index (autoincrement) and integer value. The table is millions of rows long. How can I search if a certain number appear in the last n rows of the table most efficiently?

Read More
JOIN queries vs multiple queries

Are JOIN queries faster than several queries? (You run your main query, and then you run many other SELECTs based on the results from your main query) I'm asking because JOINing them would complicate A LOT the design of my …

Read More
How to do the Recursive SELECT query in MySQL?

I got a following table: col1 | col2 | col3 -----+------+------- 1 | a | 5 5 | d | 3 3 | k | 7 6 | o | 2 2 | 0 | 8 If a user searches for "1", the program will look at the col1 that has "1" then it will get a value in col3 "5", then the program …

Read More