Do WHERE IN queries reindex the table after each set within the query?

I have a basic WHERE IN query that takes too long (30+ secs) to process.

DELETE FROM `table1`
WHERE ( djID, localID )
IN (("128","26"),("110","32"))

The table I'm querying on has about 20 million rows, with two indices - the first is the auto-incremented PK named ID, the second index is over two columns - djID, localID

I'm wondering if MySQL re-indexes the table after each ( djID, localID ) in the query, therefore accounting for the long query time, and if so, is there a way to force it to wait until all the ( djID, localID ) sets have been executed in the query before re-indexing?

Or is there another reason jumping out that may cause the long query time?

-
No SELECT causes reindexing. Only ALTER, OPTIMIZE, and a few other commands, may cause reindexing.

-
The construct (a,b) IN ((1,2), (3,4)) is not well optimized.

So,... Make it two DELETEs, such as

DELETE FROM `table1`
    WHERE djID = 128
      AND localID = 26;

If you have lots of them, you could build a tmp table with the pairs of numbers, then do a multi-table DELETE.

To demonstrate that it is doing far more work than it ought to,

FLUSH STATUS;
DELETE ... WHERE ... IN ...;
SHOW SESSION STATUS LIKE 'Handler%';

You will probably see some numbers about 20M (or maybe 40M), indicating that it did a table scan in spite of having the index.

Then file a bug with http://bugs.mysql.com (unless there already is one).

StackExchange Database Administrators Q#124820, answer score: 6