Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
There is a query in my application to delete duplicate records (specific fields), this query used to work fine on MySQL (v 8.0.21) on Windows 10 and takes about a minute,
When I migrated to MariaDB 10.5.9 on Ubuntu 20.04 , this query stucks, I left it for +6 hours and it didn't come back.
The table has about 4 million records, table is:
CREATE TABLE `item_variant_price` ( |
`seller_variant_id` int(11) NOT NULL AUTO_INCREMENT, |
`item_id` varchar(100) DEFAULT NULL, |
`price` float DEFAULT NULL, |
`seller_name` varchar(400) DEFAULT NULL, |
`variant` varchar(400) DEFAULT NULL, |
`is_fulfilled` int(11) NOT NULL DEFAULT 0, |
`insertion_date` timestamp NOT NULL DEFAULT current_timestamp(), |
`modification_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), |
`is_main_page` int(11) DEFAULT 0, |
`is_cheapest` int(11) NOT NULL DEFAULT 0, |
`variant_url` varchar(400) DEFAULT NULL, |
PRIMARY KEY (`variant_id`), |
FULLTEXT KEY `item_variant_price_serial_IDX` (`item_id`,`seller_name`,`variant`) |
) ENGINE=InnoDB AUTO_INCREMENT=4309337 DEFAULT CHARSET=utf8mb4; |
and this is the query:
delete from item_variant_price where seller_variant_id not in (select m from (select max(seller_variant_id) m from item_variant_price group by item_id, seller_name, variant) as innerTable); |
the inner select statement takes 27 seconds, so there has to be an issue with delete ?
Thanks for your support
Attachments
Issue Links
- duplicates
-
MDEV-22415 Single table UPDATE/DELETE doesn't use non-semijoin Materialization
-
- Closed
-
- relates to
-
-
- Closed
-
-
MDEV-35231 Wrong EXPLAIN or ANALYZE output: SUBQUERY instead of DEPENDENT SUBQUERY
-
- Open
-