MySQL Forums
Forum List  »  Optimizer & Parser

Slow DELETE-Statement since update on 5.7
Posted by: engram GmbH
Date: March 19, 2018 07:51AM

I have a MySQL 5.7 server with a table MEDIADATA with 2000 rows that contains one longblob:

`MEDIADATA` (
`id` bigint(20) NOT NULL,
`version` bigint(20) NOT NULL,
`crc32sum` bigint(20) NOT NULL,
`mediadata` longblob NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `crc32sum` (`crc32sum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin


When I try to delete some records with:

DELETE FROM MEDIADATA
USING MEDIADATA
INNER JOIN (SELECT id FROM MEDIADATA WHERE id NOT IN (SELECT mediaData_id FROM MEDIA)) AS temptable
ON MEDIADATA.id = temptable.id;

it takes very long time to execute (up to two minutes).

The same DELETE-Statement on a MySQL 5.6 server with exactly the same database (schema and data) takes less than one second.

In both scenarios no row is actually deleted as the result of the JOIN is an empty table.

Is there any known change that might explain the difference between 5.6 and 5.7 regarding the execution time?

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow DELETE-Statement since update on 5.7
349
March 19, 2018 07:51AM


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.