DBT3 S30: Q18 uses ICP for InnoDB but not for MyISAM

[Timour Katchaounov]

When DBT3 Q18 is run against an InnoDB database, it uses ICP, if run
with the same settings against the same MyISAM database, ICP is not used.

EXPLAINs:

InnoDB:

set @@optimizer_switch='index_condition_pushdown=on, semijoin=off';
+----+-------------+----------+-------+--------------------------------------------+-----------------------+---------+-------------------------+-----------+-------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+--------------------------------------------+-----------------------+---------+-------------------------+-----------+-------------------------------------------------------------------------------------------+
| 1 | PRIMARY | customer | ALL | PRIMARY | NULL | NULL | NULL | 4476636 | Using temporary; Using filesort |
| 1 | PRIMARY | orders | ref | PRIMARY,i_o_custkey | i_o_custkey | 5 | dbt3.customer.c_custkey | 7 | Using index condition; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan |
| 1 | PRIMARY | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | i_l_orderkey_quantity | 4 | dbt3.orders.o_orderkey | 2 | Using index |
| 2 | SUBQUERY | lineitem | index | NULL | PRIMARY | 8 | NULL | 179175334 | |
+----+-------------+----------+-------+--------------------------------------------+-----------------------+---------+-------------------------+-----------+-------------------------------------------------------------------------------------------+
(5 min 0.98 sec)

MYISAM:

set @@optimizer_switch='index_condition_pushdown=on, semijoin=off';
+----+-------------+----------+-------+--------------------------------------------+-----------------------+---------+-------------------------+-----------+---------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+--------------------------------------------+-----------------------+---------+-------------------------+-----------+---------------------------------------------------------------------------------+
| 1 | PRIMARY | customer | ALL | PRIMARY | NULL | NULL | NULL | 4500000 | Using temporary; Using filesort |
| 1 | PRIMARY | orders | ref | PRIMARY,i_o_custkey | i_o_custkey | 5 | dbt3.customer.c_custkey | 15 | Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan |
| 1 | PRIMARY | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | i_l_orderkey_quantity | 4 | dbt3.orders.o_orderkey | 4 | Using index |
| 2 | SUBQUERY | lineitem | index | NULL | i_l_orderkey_quantity | 13 | NULL | 179998372 | Using index |
+----+-------------+----------+-------+--------------------------------------------+-----------------------+---------+-------------------------+-----------+---------------------------------------------------------------------------------

[Igor Babaev (igorb-seattle)]

The query is

select sql_calc_found_rows
       c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
from customer, orders, lineitem
where

      and c_custkey = o_custkey and o_orderkey = l_orderkey
group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
order by o_totalprice desc, o_orderdate
limit 10;

When we access the table orders in both cases we use the index i_o_custkey.
In InnoDB ICP can be applied for extended keys. So acctually in innoDB when
testing applicability of ICP we consider the extended key (o_custkey, o_orderkey),
 and the condition
 o_orderkey in (select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 300)
can be pushed into this index.