3

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?

  • What mysql version do you use? – ypercubeᵀᴹ Dec 30 '15 at 7:27
  • How many rows are deleted? – paparazzo Dec 30 '15 at 11:34
6
  1. No SELECT causes reindexing. Only ALTER, OPTIMIZE, and a few other commands, may cause reindexing.

  2. 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).

| improve this answer | |
  • DELETE FROM table1 WHERE (djID = 128 AND localID = 26) OR (djID = 110 AND localID = 32) ; should use the index as well. – ypercubeᵀᴹ Dec 30 '15 at 6:53
  • If the pairs aren't many, perhaps the temporary table could be replaced with a derived one along the lines of (SELECT '128' AS djID, '26' AS localID UNION ALL SELECT '110', '32') AS tmp. – Andriy M Dec 30 '15 at 6:55
  • @YperSillyCubeᵀᴹ - the optimizer did not used to be smart enough to the obvious index. OR pretty much turned off the optimizer. Do you have evidence to the contrary now? – Rick James Dec 30 '15 at 15:53
  • Thanks guys. I didn't get to this before as I was on holiday - I will try out the suggestions this week. – Shaun Jan 4 '16 at 0:19
  • @Rick James - am I able to hire you as a consultant to help code the multi table delete? – Shaun Jan 4 '16 at 0:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.