Severe Performance Issue Caused By 920ebe9901be1866b7764749a2408734ad8e3e3e

[mpchadwick]

In the benchmarking I've done the new query is ~200X slower. It also locks and has caused production downtime on a site I'm working on.

Specs

• Server version: 5.6.38-83.0-log Percona Server (GPL), Release 83.0, Revision dc97471bd4
• 4924 rows in op_avatax_log
• 7150 in op_avatax_queue

Before:

mysql> DELETE FROM op_avatax_log WHERE created_at < DATE_SUB(UTC_DATE(), INTERVAL 1 DAY);
Query OK, 4924 rows affected (0.15 sec)

After:

mysql> DELETE FROM op_avatax_log WHERE created_at < DATE_SUB(UTC_DATE(), INTERVAL 1 DAY) AND not exists (select queue_id FROM op_avatax_queue as q where q.quote_id = op_avatax_log.quote_id);
Query OK, 4729 rows affected, 1 warning (20.69 sec)

Note that this is just staging data. Issue is much more severe in production...