[MDEV-2186] LP:898643 - DBT3 S30: Q18 uses ICP for InnoDB but not for MyISAM Created: 2011-12-01  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug
Reporter: Timour Katchaounov (Inactive) Assignee: Igor Babaev
Resolution: Not a Bug Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug898643.xml    

 Description   

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

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Comments   
Comment by Igor Babaev [ 2011-12-02 ]

Re: DBT3 S30: Q18 uses ICP for InnoDB but not for MyISAM
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.

Comment by Rasmus Johansson (Inactive) [ 2011-12-02 ]

Launchpad bug id: 898643

Generated at Thu Feb 08 06:40:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.