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.
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.