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)