MySQL, delete and index hint

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 wron开发者_Go百科g 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?

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.

I've never really come across a situation where MySQL chose the wrong index, but rather my understanding of how indexes worked was usually at fault.

You might want to check out this book: http://oreilly.com/catalog/9780596003067

It has a great section on how indexes work and other tuning options.

As stated in other answers, MySQL can't use indexes, but the PRIMARY KEY index.

So your best option, if you have a PRIMARY KEY on the table is to run a fast SELECT, then DELETE according lines. Preferably in a TRANSACTION, so that you don't delete wrong rows.

Hence:

DELETE FROM table WHERE column_with_index = 0

Will be rewritten:

SELECT primary_key FROM table WHERE column_with_index = 0 => returns many lines

DELETE FROM table WHERE primary_key IN(?, ?, ?) => ? will be replaced by the results of the SELECTed primary keys.

If you have not that much lines to delete, it would be more efficient this way.

For example, I've just hit an exemple, on the same table, with the same data:

• 7499067 rows analyzed by DELETE : 12 seconds

vs

• 6 rows analyzed by SELECT using a good index : 0.10 seconds
• 0 rows to be deleted in the end