MySQL Forums
Forum List  »  Optimizer & Parser

Re: Slow DELETE-Statement since update on 5.7
Posted by: engram GmbH
Date: March 20, 2018 02:09AM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Slow DELETE-Statement since update on 5.7
178
March 20, 2018 02:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.