|
I tried with 10000 rows, 2.8s in MariaDB 10.5.9 vs 0.07 Mysql 8.0.21. After setting optimizer_switch to 'in_to_exists=off' - MariaDB 10.5.9 -0.02
CREATE TABLE t1 (
|
id int NOT NULL PRIMARY KEY,
|
item_id varchar(100),
|
seller_name varchar(400),
|
variant varchar(400),
|
FULLTEXT KEY t1_serial_IDX (item_id,seller_name,variant)
|
);
|
|
insert into t1 select seq,seq,seq,seq from seq_1_to_10000;
|
|
analyze format=json
|
DELETE FROM t1 WHERE id NOT IN
|
(SELECT m FROM (SELECT max(id) m FROM t1 GROUP BY item_id, seller_name, variant) AS innertable);
|
|
|
MariaDB 10.5.9
|
MariaDB [test]> analyze DELETE FROM t1 WHERE id NOT IN (SELECT m FROM (SELECT max(id) m FROM t1 GROUP BY item_id, seller_name, variant) AS innertable);
|
+------+--------------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+--------------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+---------------------------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10000 | 10000.00 | 100.00 | 0.00 | Using where |
|
| 2 | DEPENDENT SUBQUERY | <derived3> | ALL | NULL | NULL | NULL | NULL | 10000 | 5000.50 | 100.00 | 0.02 | Using where |
|
| 3 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 10000 | 10000.00 | 100.00 | 100.00 | Using temporary; Using filesort |
|
+------+--------------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+---------------------------------+
|
3 rows in set (2.900 sec)
|
|
MariaDB [test]> analyze format=json DELETE FROM t1 WHERE id NOT IN (SELECT m FROM (SELECT max(id) m FROM t1 GROUP BY item_id, seller_name, variant) AS innertable);
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| ANALYZE |
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"r_total_time_ms": 2958.619018,
|
"table": {
|
"delete": 1,
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 10000,
|
"r_rows": 10000,
|
"r_filtered": 0,
|
"r_total_time_ms": 1.089836561,
|
"attached_condition": "!(<in_optimizer>(t1.`id`,<exists>(subquery#2)))"
|
},
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 10000,
|
"r_total_time_ms": 2956.083461,
|
"having_condition": "innertable.m is null",
|
"table": {
|
"table_name": "<derived3>",
|
"access_type": "ALL",
|
"r_loops": 10000,
|
"rows": 10000,
|
"r_rows": 5000.5,
|
"r_table_time_ms": 624.9038041,
|
"r_other_time_ms": 2316.295274,
|
"filtered": 100,
|
"r_filtered": 0.019998,
|
"attached_condition": "10000 = innertable.m or innertable.m is null",
|
"materialized": {
|
"query_block": {
|
"select_id": 3,
|
"r_loops": 1,
|
"r_total_time_ms": 15.52120094,
|
"filesort": {
|
"sort_key": "t1.item_id, t1.seller_name, t1.variant",
|
"r_loops": 1,
|
"r_total_time_ms": 4.314342992,
|
"r_used_priority_queue": false,
|
"r_output_rows": 10000,
|
"r_buffer_size": "2047Kb",
|
"r_sort_mode": "packed_sort_key,rowid",
|
"temporary_table": {
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 10000,
|
"r_rows": 10000,
|
"r_table_time_ms": 1.606918251,
|
"r_other_time_ms": 7.823054272,
|
"filtered": 100,
|
"r_filtered": 100
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
]
|
}
|
} |
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (2.957 sec)
|
|
|
Mysql 8.0.21
|
mysql> explain DELETE FROM t1 WHERE id NOT IN (SELECT m FROM (SELECT max(id) m FROM t1 GROUP BY item_id, seller_name, variant) AS innertable);
|
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
|
| 1 | DELETE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10000 | 100.00 | Using where |
|
| 2 | SUBQUERY | <derived3> | NULL | ALL | NULL | NULL | NULL | NULL | 10000 | 100.00 | NULL |
|
| 3 | DERIVED | t1 | NULL | ALL | t1_serial_IDX | NULL | NULL | NULL | 10000 | 100.00 | Using temporary |
|
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
|
3 rows in set, 1 warning (0.00 sec)
|
|
Note (Code 1003): delete from `test`.`t1` where <in_optimizer>(`test`.`t1`.`id`,`test`.`t1`.`id` in ( <materialize> (/* select#2 */ select `innertable`.`m` from (/* select#3 */ select max(`test`.`t1`.`id`) AS `m` from `test`.`t1` group by `test`.`t1`.`item_id`,`test`.`t1`.`seller_name`,`test`.`t1`.`variant`) `innertable` where true having true ), <primary_index_lookup>(`test`.`t1`.`id` in <temporary table> on <auto_distinct_key> where ((`test`.`t1`.`id` = `<materialized_subquery>`.`m`)))) is false)
|
|
mysql> explain analyze DELETE FROM t1 WHERE id NOT IN (SELECT m FROM (SELECT max(id) m FROM t1 GROUP BY item_id, seller_name, variant) AS innertable);
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| EXPLAIN |
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| -> Delete from t1 (immediate)
|
-> Filter: <in_optimizer>(t1.id,t1.id in (select #2) is false) (cost=1015.12 rows=10000) (actual time=75.521..75.521 rows=0 loops=1)
|
-> Table scan on t1 (cost=1015.12 rows=10000) (actual time=0.023..17.489 rows=10000 loops=1)
|
-> Select #2 (subquery in condition; run only once)
|
-> Filter: ((t1.id = `<materialized_subquery>`.m)) (actual time=0.000..0.000 rows=1 loops=10001)
|
-> Limit: 1 row(s) (actual time=0.000..0.000 rows=1 loops=10001)
|
-> Index lookup on <materialized_subquery> using <auto_distinct_key> (m=t1.id) (actual time=0.000..0.000 rows=1 loops=10001)
|
-> Materialize with deduplication (actual time=0.006..0.006 rows=1 loops=10001)
|
-> Table scan on innertable (actual time=0.001..0.336 rows=10000 loops=1)
|
-> Materialize (actual time=49.180..49.904 rows=10000 loops=1)
|
-> Table scan on <temporary> (actual time=0.001..0.652 rows=10000 loops=1)
|
-> Aggregate using temporary table (actual time=47.329..48.414 rows=10000 loops=1)
|
-> Table scan on t1 (cost=1015.12 rows=10000) (actual time=0.009..31.229 rows=10000 loops=1)
|
|
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.08 sec)
|
|
mysql> DELETE FROM t1 WHERE id NOT IN (SELECT m FROM (SELECT max(id) m FROM t1 GROUP BY item_id, seller_name, variant) AS innertable);
|
Query OK, 0 rows affected (0.07 sec)
|
with optimizer_switch='in_to_exists=off':
|
MariaDB 10.5.9
|
MariaDB [test]> set optimizer_switch='in_to_exists=off';
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> analyze DELETE FROM t1 WHERE id NOT IN (SELECT m FROM (SELECT max(id) m FROM t1 GROUP BY item_id, seller_name, variant) AS innertable);
|
+------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+---------------------------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10000 | 10000.00 | 100.00 | 0.00 | Using where |
|
| 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 10000 | 10000.00 | 100.00 | 100.00 | |
|
| 3 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 10000 | 10000.00 | 100.00 | 100.00 | Using temporary; Using filesort |
|
+------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+---------------------------------+
|
3 rows in set (0.022 sec)
|
|