12

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Manuel Darveau
  • 4,585
  • 5
  • 26
  • 36

3 Answers3

4

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.

dnagirl
  • 20,196
  • 13
  • 80
  • 123
1

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.

Brent
  • 23,354
  • 10
  • 44
  • 49
  • +1, the book opens your eyes on many things you would never think about. – newtover May 27 '10 at 20:57
  • Editing, dnagirl is right about index hint syntax, I didn't know that went live. – Brent May 27 '10 at 21:11
  • 1
    I have seen mysql choose the wrong index many times. For us it happens on a large table (15M rows) that has a lot of similar indexes (15 indexes). Maybe a crazy scenario --- there is a reason --- and it gets the wrong index often, even when the inde to choose should be the PRIMARY. – Paul Bartlett Nov 02 '16 at 01:30
  • 2
    It's difficult to trust an answer starting from «I've never really come across a situation where MySQL chose the wrong index». This is not a rare problem. This is an everyday problem for database administrators with databases with millions of rows. – Valerio Bozz Mar 04 '20 at 09:35
1

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
Yvan
  • 2,539
  • 26
  • 28