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 -
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?