Mysql DELETE (with NOT IN subselect) not using index and getting ERROR 1205 (HY000): Lock wait timeout exceeded;

Question

My ticker table has 9M rows and I need to delete 271k rows on that table, for one day (2018-06-26). Is there a reason why my DELETE is not using an index and the query is abending with?

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

How can I make this DELETE work?

Query:

DELETE FROM `tickers` 
WHERE
      `created_at` between '2018-06-26T00:00:00' and '2018-06-26T23:59:59'
  AND id  NOT IN 
    (SELECT MAX(id) FROM (select * FROM `tickers`) as t2 
          WHERE `created_at` between '2018-06-26T00:00:00' and '2018-06-26T23:59:59'
         GROUP BY
          exchange
         , base_currency
         , quote_currency
         , DATE(created_at)
        );

Indexes:

index#1 unique index on id
index#2 index on exchange, base_currency, quote_currency, created_at
index#3 index on created_at

Explain:

+------+--------------------+------------+-------+--------------------------+--------------------------+---------+------+---------+------------------------------+
| id   | select_type        | table      | type  | possible_keys            | key                      | key_len | ref  | rows    | Extra                        |
+------+--------------------+------------+-------+--------------------------+--------------------------+---------+------+---------+------------------------------+
|    1 | PRIMARY            | tickers    | range | tickers_created_at_index | tickers_created_at_index | 5       | NULL |  554300 | Using where                  |
|    2 | DEPENDENT SUBQUERY | <derived3> | ALL   | NULL                     | NULL                     | NULL    | NULL | 8712002 | Using where; Using temporary |
|    3 | DERIVED            | tickers    | ALL   | NULL                     | NULL                     | NULL    | NULL | 8712002 |                              |
+------+--------------------+------------+-------+--------------------------+--------------------------+---------+------+---------+------------------------------+

The same query with a SELECT instead of DELETE works:

SELECT id FROM `tickers` 
WHERE
      `created_at` between '2018-06-26T00:00:00' and '2018-06-26T23:59:59'
  AND id  NOT IN 
    (SELECT MAX(id) FROM (select * FROM `tickers`) as t2 
          WHERE `created_at` between '2018-06-26T00:00:00' and '2018-06-26T23:59:59'
         GROUP BY
          exchange
         , base_currency
         , quote_currency
         , DATE(created_at)
        );

Proposed solution #1:

DELETE t
FROM tickers t LEFT JOIN
     (SELECT MAX(t2.id) as max_id
      FROM tickers t2 
      WHERE t2.created_at between '2018-06-26T00:00:00' and '2018-06-26T23:59:59'
      GROUP BY exchange, base_currency, quote_currency, DATE(created_at)
     ) t2
     ON t2.max_id = t.id
WHERE t.created_at between '2018-06-26T00:00:00' and '2018-06-26T23:59:59' AND
      t2.max_id IS NULL;

Explain of proposed solution #1:

+------+-------------+------------+-------+--------------------------+--------------------------+---------+----------------------+--------+----------------------------------------------+
| id   | select_type | table      | type  | possible_keys            | key                      | key_len | ref                  | rows   | Extra                                        |
+------+-------------+------------+-------+--------------------------+--------------------------+---------+----------------------+--------+----------------------------------------------+
|    1 | PRIMARY     | t          | range | tickers_created_at_index | tickers_created_at_index | 5       | NULL                 | 554300 | Using where                                  |
|    1 | PRIMARY     | <derived2> | ref   | key0                     | key0                     | 5       | dbdevinputspike.t.id |     10 | Using where                                  |
|    2 | DERIVED     | t2         | range | tickers_created_at_index | tickers_created_at_index | 5       | NULL                 | 554300 | Using where; Using temporary; Using filesort |
+------+-------------+------------+-------+--------------------------+--------------------------+---------+----------------------+--------+----------------------------------------------+

Result of proposed solution #1:

First got: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.
Then, noticed that a process was running (SHOW PROCESSLIST;).
Killed the process (KILL <process id>;) and query ran successfully.

Proposed solution #2:

CREATE TEMPORARY TABLE IF NOT EXISTS tickers_temp AS 
(
SELECT id FROM `tickers` 
WHERE
      `created_at` between '2018-06-28T00:00:00' and '2018-06-28T23:59:59'
  AND id  NOT IN 
    (SELECT MAX(id) FROM (select * FROM `tickers`) as t2 
          WHERE `created_at` between '2018-06-28T00:00:00' and '2018-06-28T23:59:59'
         GROUP BY
          exchange
         , base_currency
         , quote_currency
         , DATE(created_at)
        )
)   ;   


DELETE FROM `tickers` WHERE id IN (SELECT id FROM `tickers_temp`);

Result of proposed solution #2:

Running for a looong time. Killed the process.

Answer 1

Try using join:

DELETE t
    FROM tickers t LEFT JOIN
         (SELECT MAX(t2.id) as max_id
          FROM tickers t2 
          WHERE t2.created_at between '2018-06-26T00:00:00' and '2018-06-26T23:59:59'
          GROUP BY exchange, base_currency, quote_currency, DATE(created_at)
         ) t2
         ON t2.max_id = t.id
    WHERE t.created_at between '2018-06-26T00:00:00' and '2018-06-26T23:59:59' AND
          t2.max_id IS NULL;