Executive Summary: A query like TPC-H Query 17 can be sped up by large factors by using straight_joins and clustering indexes. (This entry posted by Dave.)
In a previous post, we wrote about queries like TPC-H query 2, and the use of straight_join to improve performance.
This week, we consider Query 17, described by the TPC-H documentation as
“The Small-Quantity-Order Revenue Query considers parts of a given brand and with a given container type and determines the average lineitem quantity of such parts ordered for all orders (past and pending) in the 7-year database. What would the average yearly gross (undiscounted) loss in revenue if orders for these parts with a quantity of this average were no longer taken?”
Our initial run on Q17 (same hardware as before) timed out after 3600 seconds.
The MySQL query is:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#33' and p_container = 'WRAP PACK' and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey ); |
The query plan was:
|
1 2 3 4 5 6 7 8 9 10 |
mysql> explain select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part -> where p_partkey = l_partkey and p_brand = 'Brand#33' and p_container = 'WRAP PACK' and l_quantity < (select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey); +----+--------------------+----------+--------+---------------+---------+---------+-----------------------------------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+----------+--------+---------------+---------+---------+-----------------------------------+----------+-------------+ | 1 | PRIMARY | lineitem | ALL | NULL | NULL | NULL | NULL | 59986052 | | | 1 | PRIMARY | part | eq_ref | PRIMARY | PRIMARY | 4 | tpch10G_tokudb.lineitem.l_partkey | 1 | Using where | | 2 | DEPENDENT SUBQUERY | lineitem | ALL | NULL | NULL | NULL | NULL | 59986052 | Using where | +----+--------------------+----------+--------+---------------+---------+---------+-----------------------------------+----------+-------------+ |
This plan does not take advantage of the selectivity of picking a specific brand and container to reduce the search set.
Using STRAIGHT_JOIN (as described in our post on Q2) and ordering the FROM clause to select the part table first sets the plan on a better course.
|
1 2 3 4 5 6 7 8 9 10 |
mysql> explain select straight_join sum(l_extendedprice) / 7.0 as avg_yearly from part, lineitem -> where p_partkey = l_partkey and p_brand = 'Brand#33' and p_container = 'WRAP PACK' and l_quantity < (select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey); +----+--------------------+----------+------+---------------+------+---------+------+----------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+----------+------+---------------+------+---------+------+----------+--------------------------------+ | 1 | PRIMARY | part | ALL | PRIMARY | NULL | NULL | NULL | 2000000 | Using where | | 1 | PRIMARY | lineitem | ALL | NULL | NULL | NULL | NULL | 59986052 | Using where; Using join buffer | | 2 | DEPENDENT SUBQUERY | lineitem | ALL | NULL | NULL | NULL | NULL | 59986052 | Using where | +----+--------------------+----------+------+---------------+------+---------+------+----------+--------------------------------+ |
However, the query bogs down when searching the lineitem table.
Although an index was available in the LINEITEM table to quickly search the table (lineitem_fk3), the subsequent data lookup (along the primary key) caused a long(!) series of point queries.
Unlike many storage engines, TokuDB supports clustering indexes on any index (not just the primary key), and further supports clustering indexes on more than one index per table.
|
1 |
mysql > create clustering index lineitem_ck3 on lineitem (l_partkey, l_suppkey); |
This results in the following query/plan:
|
1 2 3 4 5 6 7 8 9 |
mysql> explain select straight_join sum(l_extendedprice) / 7.0 as avg_yearly from part, lineitem -> where p_partkey = l_partkey and p_brand = 'Brand#33' and p_container = 'WRAP PACK' and l_quantity < (select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey); +----+--------------------+----------+------+---------------+--------------+---------+-------------------------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+----------+------+---------------+--------------+---------+-------------------------------+---------+-------------+ | 1 | PRIMARY | part | ALL | PRIMARY | NULL | NULL | NULL | 2000000 | Using where | | 1 | PRIMARY | lineitem | ref | lineitem_ck3 | lineitem_ck3 | 4 | tpch10G_tokudb.part.p_partkey | 599860 | Using where | | 2 | DEPENDENT SUBQUERY | lineitem | ref | lineitem_ck3 | lineitem_ck3 | 4 | tpch10G_tokudb.part.p_partkey | 5998606 | | +----+--------------------+----------+------+---------------+--------------+---------+-------------------------------+---------+-------------+ |
Although it appears to look at more rows, each table is scanned efficiently down a key that provides all of the information needed to address the query.
We measured 101 sec on this query. Maybe some day we’ll be patient enough to let the original plan complete, but we know this approach is at least 36 (3600/101) times faster.
