|
Thank you, cool project!
I added all queries for mariadb together (sorted by execution time, that was reported) (ma.sql), just for easier use. I also ran them for MariDB 10.5.9 (res_mdb file) and Mysql 8.0.21 (res-mysql file), so if opening files with some diff/meld side by side, it is easier to see execution time. (Some queries hang in mysql so they were killed).
There are a lot of queries with similar problems, I will add some examples below, so queries of this type should probably be excluded from a follow-up report.
1)Impossible WHERE
|
MariaDB 10.5.9
|
MariaDB [test]> analyze select subq_0.c0 as c0, subq_0.c0 as c1, 87 as c2, subq_0.c0 as c3, subq_0.c0 as c4, subq_0.c0 as c5 from (select 46 as c0 from district as ref_0 where EXISTS ( select ref_0.d_street_2 as c0, (select ol_d_id from order_line limit 1 offset 4) as c1, ref_1.ol_w_id as c2, (select h_data from history limit 1 offset 3) as c3, 79 as c4 from order_line as ref_1 where EXISTS ( select ref_0.d_w_id as c0, ref_2.w_id as c1, ref_2.w_state as c2, ref_1.ol_amount as c3, ref_1.ol_supply_w_id as c4 from warehouse as ref_2 where ref_1.ol_quantity is NULL ) ) ) as subq_0 where (select i_id from item limit 1 offset 6) is not NULL;
|
|
+------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+
|
| 1 | PRIMARY | ref_0 | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 0.00 | Using where |
|
| 7 | SUBQUERY | item | ALL | NULL | NULL | NULL | NULL | 99438 | 7.00 | 100.00 | 100.00 | |
|
| 3 | DEPENDENT SUBQUERY | ref_1 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 0.00 | Using where |
|
| 6 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
| 5 | SUBQUERY | history | ALL | NULL | NULL | NULL | NULL | 30056 | NULL | 100.00 | NULL | |
|
| 4 | SUBQUERY | order_line | ALL | NULL | NULL | NULL | NULL | 298732 | NULL | 100.00 | NULL | |
|
+------+--------------------+------------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------+
|
6 rows in set (1.207 sec)
|
|
mysql 8.0.21
|
mysql> explain analyze select subq_0.c0 as c0, subq_0.c0 as c1, 87 as c2, subq_0.c0 as c3, subq_0.c0 as c4, subq_0.c0 as c5 from (select 46 as c0 from district as ref_0 where EXISTS ( select ref_0.d_street_2 as c0, (select ol_d_id from order_line limit 1 offset 4) as c1, ref_1.ol_w_id as c2, (select h_data from history limit 1 offset 3) as c3, 79 as c4 from order_line as ref_1 where EXISTS ( select ref_0.d_w_id as c0, ref_2.w_id as c1, ref_2.w_state as c2, ref_1.ol_amount as c3, ref_1.ol_supply_w_id as c4 from warehouse as ref_2 where ref_1.ol_quantity is NULL ) ) ) as subq_0 where (select i_id from item limit 1 offset 6) is not NULL;
|
+-----------------------------------------------------------------------------+
|
| EXPLAIN |
|
+-----------------------------------------------------------------------------+
|
| -> Zero rows (Impossible WHERE) (actual time=0.000..0.000 rows=0 loops=1)
|
|
|
+-----------------------------------------------------------------------------+
|
1 row in set, 5 warnings (0.00 sec)
|
|
Note (Code 1276): Field or reference 'test.ref_0.d_street_2' of SELECT #3 was resolved in SELECT #2
|
Note (Code 1276): Field or reference 'test.ref_0.d_w_id' of SELECT #6 was resolved in SELECT #2
|
Note (Code 1276): Field or reference 'test.ref_1.ol_amount' of SELECT #6 was resolved in SELECT #3
|
Note (Code 1276): Field or reference 'test.ref_1.ol_supply_w_id' of SELECT #6 was resolved in SELECT #3
|
Note (Code 1276): Field or reference 'test.ref_1.ol_quantity' of SELECT #6 was resolved in SELECT #3
|
|
|
|
-- source include/have_innodb.inc
|
|
CREATE TABLE `new_order` (
|
`no_w_id` int(11) NOT NULL,
|
`no_d_id` int(11) NOT NULL,
|
`no_o_id` int(11) NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
#INSERT ..
|
|
analyze table new_order;
|
|
#explain
|
analyze
|
select ref_0.no_o_id as c0,
|
ref_1.no_w_id as c1,
|
ref_0.no_d_id as c2,
|
ref_0.no_o_id as c3,
|
ref_1.no_w_id as c4,
|
cast(coalesce(ref_1.no_d_id, ref_1.no_w_id) as signed) as c5,
|
ref_1.no_o_id as c6,
|
36 as c7,
|
ref_1.no_d_id as c8,
|
ref_1.no_w_id as c9
|
from new_order as ref_0
|
inner join new_order as ref_1 on (ref_0.no_o_id = ref_1.no_w_id)
|
where ref_0.no_o_id is not null ;
|
|
MariaDB 10.5.9
MariaDB [test]> analyze
|
-> select ref_0.no_o_id as c0,
|
-> ref_1.no_w_id as c1,
|
-> ref_0.no_d_id as c2,
|
-> ref_0.no_o_id as c3,
|
-> ref_1.no_w_id as c4,
|
-> cast(coalesce(ref_1.no_d_id, ref_1.no_w_id) as signed) as c5,
|
-> ref_1.no_o_id as c6,
|
-> 36 as c7,
|
-> ref_1.no_d_id as c8,
|
-> ref_1.no_w_id as c9
|
-> from new_order as ref_0
|
-> inner join new_order as ref_1 on (ref_0.no_o_id = ref_1.no_w_id)
|
-> where ref_0.no_o_id is not null ;
|
+------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+
|
| 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 100.00 | Using where |
|
| 1 | SIMPLE | ref_1 | ALL | NULL | NULL | NULL | NULL | 7999 | 8019.00 | 100.00 | 0.00 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+------+---------------+------+---------+------+------+---------+----------+------------+-------------------------------------------------+
|
2 rows in set (2.663 sec)
|
|
MariaDB [test]> analyze format=json select ref_0.no_o_id as c0, ref_1.no_w_id as c1, ref_0.no_d_id as c2, ref_0.no_o_id as c3, ref_1.no_w_id as c4, cast(coalesce(ref_1.no_d_id, ref_1.no_w_id) as signed) as c5, ref_1.no_o_id as c6, 36 as c7, ref_1.no_d_id as c8, ref_1.no_w_id as c9 from new_order as ref_0 inner join new_order as ref_1 on (ref_0.no_o_id = ref_1.no_w_id) where ref_0.no_o_id is not null;
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| ANALYZE |
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 2683.323352,
|
"table": {
|
"table_name": "ref_0",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 7999,
|
"r_rows": 8019,
|
"r_table_time_ms": 7.834094645,
|
"r_other_time_ms": 2.152161432,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "ref_0.no_o_id is not null"
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "ref_1",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 7999,
|
"r_rows": 8019,
|
"r_table_time_ms": 9.332794815,
|
"r_other_time_ms": 2663.994861,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"buffer_type": "flat",
|
"buffer_size": "78Kb",
|
"join_type": "BNL",
|
"attached_condition": "ref_1.no_w_id = ref_0.no_o_id",
|
"r_filtered": 0
|
}
|
}
|
} |
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (2.677 sec)
|
mysql 8.0.21
mysql> explain select ref_0.no_o_id as c0, ref_1.no_w_id as c1, ref_0.no_d_id as c2, ref_0.no_o_id as c3, ref_1.no_w_id as c4, cast(coalesce(ref_1.no_d_id, ref_1.no_w_id) as signed) as c5, ref_1.no_o_id as c6, 36 as c7, ref_1.no_d_id as c8, ref_1.no_w_id as c9 from new_order as ref_0 inner join new_order as ref_1 on (ref_0.no_o_id = ref_1.no_w_id) where ref_0.no_o_id is not null;
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|
| 1 | SIMPLE | ref_0 | NULL | ALL | NULL | NULL | NULL | NULL | 7999 | 100.00 | NULL |
|
| 1 | SIMPLE | ref_1 | NULL | ALL | NULL | NULL | NULL | NULL | 7999 | 10.00 | Using where; Using join buffer (hash join) |
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|
2 rows in set, 1 warning (0.00 sec)
|
|
Note (Code 1003): /* select#1 */ select `test`.`ref_0`.`no_o_id` AS `c0`,`test`.`ref_1`.`no_w_id` AS `c1`,`test`.`ref_0`.`no_d_id` AS `c2`,`test`.`ref_0`.`no_o_id` AS `c3`,`test`.`ref_1`.`no_w_id` AS `c4`,cast(coalesce(`test`.`ref_1`.`no_d_id`,`test`.`ref_1`.`no_w_id`) as signed) AS `c5`,`test`.`ref_1`.`no_o_id` AS `c6`,36 AS `c7`,`test`.`ref_1`.`no_d_id` AS `c8`,`test`.`ref_1`.`no_w_id` AS `c9` from `test`.`new_order` `ref_0` join `test`.`new_order` `ref_1` where (`test`.`ref_1`.`no_w_id` = `test`.`ref_0`.`no_o_id`)
|
mysql> explain analyze select ref_0.no_o_id as c0, ref_1.no_w_id as c1, ref_0.no_d_id as c2, ref_0.no_o_id as c3, ref_1.no_w_id as c4, cast(coalesce(ref_1.no_d_id, ref_1.no_w_id) as signed) as c5, ref_1.no_o_id as c6, 36 as c7, ref_1.no_d_id as c8, ref_1.no_w_id as c9 from new_order as ref_0 inner join new_order as ref_1 on (ref_0.no_o_id = ref_1.no_w_id) where ref_0.no_o_id is not null;
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| EXPLAIN |
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| -> Inner hash join (ref_1.no_w_id = ref_0.no_o_id) (cost=6399211.78 rows=6398400) (actual time=37.628..37.628 rows=0 loops=1)
|
-> Table scan on ref_1 (cost=0.01 rows=7999) (actual time=0.021..11.545 rows=8019 loops=1)
|
-> Hash
|
-> Table scan on ref_0 (cost=805.15 rows=7999) (actual time=0.036..10.272 rows=8019 loops=1)
|
|
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.04 sec)
|
|
|
3) queries of type SELECT DISTINCT..
CREATE TABLE `order_line` (
|
`ol_w_id` int(11) NOT NULL,
|
`ol_d_id` int(11) NOT NULL,
|
`ol_o_id` int(11) NOT NULL,
|
`ol_number` int(11) NOT NULL,
|
`ol_i_id` int(11) NOT NULL,
|
`ol_delivery_d` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
`ol_amount` decimal(6,2) NOT NULL,
|
`ol_supply_w_id` int(11) NOT NULL,
|
`ol_quantity` decimal(2,0) NOT NULL,
|
`ol_dist_info` char(24) NOT NULL
|
) ENGINE=InnoDB;
|
|
#insert ...
|
|
MariaDB 10.5.9
|
MariaDB [test]> analyze SELECT DISTINCT ref_0.ol_number AS c0,
|
-> ref_0.ol_dist_info AS c1,
|
-> ref_0.ol_quantity AS c2,
|
-> ref_0.ol_number AS c3,
|
-> ref_0.ol_i_id AS c4,
|
-> ref_0.ol_d_id AS c5
|
-> FROM order_line AS ref_0
|
-> WHERE ref_0.ol_o_id IS NOT NULL;
|
+------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+
|
| 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 100.00 | Using where; Using temporary |
|
+------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+------------------------------+
|
1 row in set (0.826 sec)
|
|
MariaDB [test]> analyze SELECT DISTINCT ref_0.ol_number AS c0,ref_0.ol_dist_info AS c1, ref_0.ol_quantity AS c2, ref_0.ol_number AS c3, ref_0.ol_i_id AS c4, ref_0.ol_d_id AS c5 FROM order_line AS ref_0;
|
|
+------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-----------------+
|
| 1 | SIMPLE | ref_0 | ALL | NULL | NULL | NULL | NULL | 298732 | 300148.00 | 100.00 | 100.00 | Using temporary |
|
+------+-------------+-------+------+---------------+------+---------+------+--------+-----------+----------+------------+-----------------+
|
1 row in set (0.841 sec)
|
|
|
Mysql 8.0.21
|
mysql> explain analyze SELECT DISTINCT ref_0.ol_number AS c0, ref_0.ol_dist_info AS c1, ref_0.ol_quantity AS c2, ref_0.ol_number AS c3,
|
ref_0.ol_i_id AS c4, ref_0.ol_d_id AS c5 FROM order_line AS ref_0 WHERE ref_0.ol_o_id IS NOT NULL;
|
--------------
|
explain analyze SELECT DISTINCT ref_0.ol_number AS c0, ref_0.ol_dist_info AS c1, ref_0.ol_quantity AS c2, ref_0.ol_number AS c3, ref_0.ol_i_id AS c4, ref_0.ol_d_id AS c5 FROM order_line AS ref_0 WHERE ref_0.ol_o_id IS NOT NULL
|
--------------
|
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| EXPLAIN |
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| -> Table scan on <temporary> (actual time=0.001..8.880 rows=300148 loops=1)
|
-> Temporary table with deduplication (actual time=308.472..328.827 rows=300148 loops=1)
|
-> Table scan on ref_0 (cost=30282.20 rows=298732) (actual time=0.038..132.458 rows=300148 loops=1)
|
|
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.38 sec)
|
|
|
Alice,
1) Thanks for your time in testing the reported queries. We will apply filter rules to remove any duplicated cases that you indicated. Since we have additional samples, hopefully, we would be able to report another unique case.
2) Here, you are comparing MariaDB with Mysql, but we would like to emphasize that there were cases where both MariaDB and MySQL ran much slower than other DBMSs.
3) Question: when we make a follow-up report, should we make a new issue or add more files to this issue thread?
Best,
Jinho Jung
|
|
I compared MariaDB to Mysql just because it was easier for me to see ways of execution. But I added all queries from the report, so it will make sense to review them afterward.
Also, I ran "ANALYZE table .." before running queries - it might be the reason that my results are slightly better than reported ones (besides different hardware, OS))
I guess it is better to open a new report, just link it to this one.
|
ma.sql
res_mdb