Slow DELETE-Statement since update on 5.7
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?
Subject
Views
Written By
Posted
Slow DELETE-Statement since update on 5.7
2047
March 19, 2018 07:51AM
837
March 19, 2018 11:40AM
970
March 19, 2018 01:15PM
908
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.