Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
I am looking at Q4.
ts ( select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate ) group by o_orderpriority order by o_orderpriority;
|
+------+--------------------+----------+-------+--------------------------------------------+---------------+---------+---------------------------+--------+----------+---------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+----------+-------+--------------------------------------------+---------------+---------+---------------------------+--------+----------+---------------------------------------------------------------------+
|
| 1 | PRIMARY | orders | range | i_o_orderdate | i_o_orderdate | 4 | NULL | 137994 | 100.00 | Using index condition; Using where; Using temporary; Using filesort |
|
| 2 | DEPENDENT SUBQUERY | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4 | dbt3sf1.orders.o_orderkey | 2 | 100.00 | Using where |
|
+------+--------------------+----------+-------+--------------------------------------------+---------------+---------+---------------------------+--------+----------+---------------------------------------------------------------------+
|
2 rows in set, 2 warnings (0.00 sec)
|
|
|
MariaDB [dbt3sf1]> set optimizer_switch='exists_to_in=on';
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [dbt3sf1]> explain extended select o_orderpriority, count(*) as order_count from orders where o_orderdate >= '1995-06-06' and o_orderdate < date_add('1995-06-06', interval 3 month) and exists ( select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate ) group by o_orderpriority order by o_orderpriority;
|
+------+-------------+----------+-------+--------------------------------------------+---------------+---------+---------------------------+--------+----------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+----------+-------+--------------------------------------------+---------------+---------+---------------------------+--------+----------+--------------------------------------------------------+
|
| 1 | PRIMARY | orders | range | PRIMARY,i_o_orderdate | i_o_orderdate | 4 | NULL | 137994 | 100.00 | Using index condition; Using temporary; Using filesort |
|
| 1 | PRIMARY | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4 | dbt3sf1.orders.o_orderkey | 2 | 100.00 | Using where; FirstMatch(orders) |
|
+------+-------------+----------+-------+--------------------------------------------+---------------+---------+---------------------------+--------+----------+--------------------------------------------------------+
|
2 rows in set, 2 warnings (0.00 sec)
|
The query plan is similar. However, the second query should be able to use BKA. Run the query with and without BKA and measure the impact.
