Re: Slow DELETE-Statement since update on 5.7

[engram GmbH]

Turning off the derived_merge feature did the trick. Execution time is back to less than one second.

Here are the EXPLAINs. Unfortunately I'm not that good in reading explain plans. All I can see is that there is some difference.

Note: The DELETE-Statement is a little more complicated than in my first post. I shortened it to have a simpler example.


SET SESSION optimizer_switch='derived_merge=ON';

EXPLAIN DELETE FROM MEDIADATA USING MEDIADATA INNER JOIN ( SELECT id FROM MEDIADATA WHERE id NOT IN ( SELECT mediaData_id FROM MEDIA) AND id NOT IN ( SELECT contentDefinitionData_id FROM MEDIA WHERE contentDefinitionData_id IS NOT NULL) AND id NOT IN ( SELECT thumbnailData_id FROM MEDIA WHERE thumbnailData_id IS NOT NULL)) AS temptable ON MEDIADATA.id = temptable.id;

+----+-------------+-----------+------------+--------+-------------------+-------------------+---------+-------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+--------+-------------------+-------------------+---------+-------------------+------+----------+--------------------------+
| 1 | DELETE | MEDIADATA | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1813 | 100.00 | NULL |
| 1 | PRIMARY | MEDIADATA | NULL | eq_ref | PRIMARY | PRIMARY | 8 | ekms.MEDIADATA.id | 1 | 100.00 | Using where; Using index |
| 5 | SUBQUERY | MEDIA | NULL | range | FK45D77C42CE2B1A3 | FK45D77C42CE2B1A3 | 9 | NULL | 1475 | 100.00 | Using where; Using index |
| 4 | SUBQUERY | MEDIA | NULL | range | FK45D77C4EDA6C8A3 | FK45D77C4EDA6C8A3 | 9 | NULL | 22 | 100.00 | Using where; Using index |
| 3 | SUBQUERY | MEDIA | NULL | index | FK45D77C42C474BAB | FK45D77C42C474BAB | 8 | NULL | 2052 | 100.00 | Using index |
+----+-------------+-----------+------------+--------+-------------------+-------------------+---------+-------------------+------+----------+--------------------------+
5 rows in set (0.01 sec)



SET SESSION optimizer_switch='derived_merge=OFF';

EXPLAIN DELETE FROM MEDIADATA USING MEDIADATA INNER JOIN ( SELECT id FROM MEDIADATA WHERE id NOT IN ( SELECT mediaData_id FROM MEDIA) AND id NOT IN ( SELECT contentDefinitionData_id FROM MEDIA WHERE contentDefinitionData_id IS NOT NULL) AND id NOT IN ( SELECT thumbnailData_id FROM MEDIA WHERE thumbnailData_id IS NOT NULL)) AS temptable ON MEDIADATA.id = temptable.id;
+----+-------------+------------+------------+--------+-------------------+-------------------+---------+--------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+-------------------+-------------------+---------+--------------+------+----------+--------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 1813 | 100.00 | NULL |
| 1 | DELETE | MEDIADATA | NULL | eq_ref | PRIMARY | PRIMARY | 8 | temptable.id | 1 | 100.00 | NULL |
| 2 | DERIVED | MEDIADATA | NULL | index | NULL | crc32sum | 8 | NULL | 1813 | 100.00 | Using where; Using index |
| 5 | SUBQUERY | MEDIA | NULL | range | FK45D77C42CE2B1A3 | FK45D77C42CE2B1A3 | 9 | NULL | 1475 | 100.00 | Using where; Using index |
| 4 | SUBQUERY | MEDIA | NULL | range | FK45D77C4EDA6C8A3 | FK45D77C4EDA6C8A3 | 9 | NULL | 22 | 100.00 | Using where; Using index |
| 3 | SUBQUERY | MEDIA | NULL | index | FK45D77C42C474BAB | FK45D77C42C474BAB | 8 | NULL | 2052 | 100.00 | Using index |
+----+-------------+------------+------------+--------+-------------------+-------------------+---------+--------------+------+----------+--------------------------+
6 rows in set (0.00 sec)