Another look at improving TPC-H-like queries – Q17

June 24, 2009

Author

kuszmaul

MySQL

Share this Post:

Summary: An alternate approach, offered in response to our original post, provides excellent improvements for smaller databases, but clustered indexes offer better performance as database size increases. (This posting is by Dave.)

Jay Pipes suggested an alternate approach to improving MySQL performance of Query 17 on a TPC-H-like database.

Add the index (l_partkey, l_quantity) to the lineitem table.

Re-write the query as:

<br>select <br>   sum(li.l_extendedprice) / 7.0 as avg_yearly <br>from lineitem li <br>   inner join part p on li.l_partkey = p.p_partkey <br>   inner join ( select <br>                   l_partkey, 0.2 * avg(l_quantity) as quantity <br>                from lineitem <br>                group by l_partkey <br>              ) as quantities <br>      on li.l_partkey = quantities.l_partkey and li.l_quantity < quantities.quantity <br>where <br>   p.p_brand = 'Brand#33'<br>   p.p_container = 'WRAP PACK';<br>

select sum(li.l_extendedprice) / 7.0 as avg_yearly from lineitem li inner join part p on li.l_partkey = p.p_partkey inner join ( select l_partkey, 0.2 * avg(l_quantity) as quantity from lineitem group by l_partkey ) as quantities on li.l_partkey = quantities.l_partkey and li.l_quantity < quantities.quantity where p.p_brand = 'Brand#33' p.p_container = 'WRAP PACK';

I ran this suggestion on MySQL with Tokutek’s TokuDB storage engine (as before) against a 10G Scale Factor database. After waiting several hours for the EXPLAIN to complete, I punted and moved to a smaller, 1G Scale Factor database. This database nearly fits in memory, and allowed me to examine the query plan

Create the index:

<br>mysql> alter table lineitem add index li_pkey_quan_idx(l_partkey, l_quantity);<br>

1	<br>mysql> alter table lineitem add index li_pkey_quan_idx(l_partkey, l_quantity);<br>

Explain:

<br>mysql> explain select sum(li.l_extendedprice) / 7.0 as avg_yearly from lineitem li<br>    -> inner join part p on li.l_partkey = p.p_partkey<br>    -> inner join ( select l_partkey, 0.2 * avg(l_quantity) as quantity from lineitem group by l_partkey ) as quantities<br>    -> on li.l_partkey = quantities.l_partkey and li.l_quantity < quantities.quantity<br>    -> where p.p_brand = 'Brand#33' and p.p_container = 'WRAP PACK';<br>+----+-------------+------------+--------+-------------------------------+--------------+---------+----------------------+---------+-------------+<br>| id | select_type | table      | type   | possible_keys                 | key          | key_len | ref                  | rows    | Extra       |<br>+----+-------------+------------+--------+-------------------------------+--------------+---------+----------------------+---------+-------------+<br>|  1 | PRIMARY     | derived2   | ALL    | NULL                          | NULL         | NULL    | NULL                 |  200000 |             | <br>|  1 | PRIMARY     | p          | eq_ref | PRIMARY                       | PRIMARY      | 4       | quantities.l_partkey |       1 | Using where | <br>|  1 | PRIMARY     | li         | ref    | lineitem_fk3,li_pkey_quan_idx | lineitem_fk3 | 4       | quantities.l_partkey |   60012 | Using where | <br>|  2 | DERIVED     | lineitem   | index  | NULL                          | lineitem_fk3 | 8       | NULL                 | 6001215 |             | <br>+----+-------------+------------+--------+-------------------------------+--------------+---------+----------------------+---------+-------------+<br>4 rows in set (1 min 18.85 sec)<br>

Notice that although the planner sees the li_pkey_quan_idx, it does not use it.

Run the Query:

<br>mysql> select sum(li.l_extendedprice) / 7.0 as avg_yearly from lineitem li<br>    -> inner join part p on li.l_partkey = p.p_partkey<br>    -> inner join ( select l_partkey, 0.2 * avg(l_quantity) as quantity from lineitem group by l_partkey ) as quantities<br>    -> on li.l_partkey = quantities.l_partkey and li.l_quantity < quantities.quantity<br>    -> where p.p_brand = 'Brand#33' and p.p_container = 'WRAP PACK';<br>+---------------+<br>| avg_yearly    |<br>+---------------+<br>| 312967.325714 | <br>+---------------+<br>1 row in set (1 min 6.73 sec)<br>

mysql> select sum(li.l_extendedprice) / 7.0 as avg_yearly from lineitem li -> inner join part p on li.l_partkey = p.p_partkey -> inner join ( select l_partkey, 0.2 * avg(l_quantity) as quantity from lineitem group by l_partkey ) as quantities -> on li.l_partkey = quantities.l_partkey and li.l_quantity < quantities.quantity -> where p.p_brand = 'Brand#33' and p.p_container = 'WRAP PACK'; +---------------+ | avg_yearly | +---------------+ | 312967.325714 | +---------------+ 1 row in set (1 min 6.73 sec)

The resulting query time (66.73s) is an improvement on the default query (155.83s). Nonetheless, when you run this query against the larger, 10G Scale Factor (SF) database, it does not complete after several hours. I observed that the intermediate tables can be held in memory for SF = 1G, but are written to disk for SF = 10G. This is a well-known peril of intermediate tables.

Undeterred, I tried forcing the query planner to make use of the li_pkey_quan_idx. I found that telling the planner to use the index in both the 3rd and 4th line of plan, I could get substantial speed-ups:

Explain:

<br>mysql> explain select sum(li.l_extendedprice) / 7.0 as avg_yearly from lineitem li use index (li_pkey_quan_idx)<br>    -> inner join part p on li.l_partkey = p.p_partkey<br>    -> inner join ( select l_partkey, 0.2 * avg(l_quantity) as quantity from lineitem use index (li_pkey_quan_idx) group by l_partkey ) as quantities<br>    -> on li.l_partkey = quantities.l_partkey and li.l_quantity < quantities.quantity<br>    -> where p.p_brand = 'Brand#33' and p.p_container = 'WRAP PACK';<br>+----+-------------+------------+--------+------------------+------------------+---------+----------------------+---------+-------------+<br>| id | select_type | table      | type   | possible_keys    | key              | key_len | ref                  | rows    | Extra       |<br>+----+-------------+------------+--------+------------------+------------------+---------+----------------------+---------+-------------+<br>|  1 | PRIMARY     | derived2   | ALL    | NULL             | NULL             | NULL    | NULL                 |  200000 |             | <br>|  1 | PRIMARY     | p          | eq_ref | PRIMARY          | PRIMARY          | 4       | quantities.l_partkey |       1 | Using where | <br>|  1 | PRIMARY     | li         | ref    | li_pkey_quan_idx | li_pkey_quan_idx | 4       | quantities.l_partkey |   60012 | Using where | <br>|  2 | DERIVED     | lineitem   | index  | NULL             | li_pkey_quan_idx | 11      | NULL                 | 6001215 | Using index | <br>+----+-------------+------------+--------+------------------+------------------+---------+----------------------+---------+-------------+<br>4 rows in set (5.74 sec)<br>

Query:

<br>mysql> select sum(li.l_extendedprice) / 7.0 as avg_yearly from lineitem li use index (li_pkey_quan_idx)<br>    -> inner join part p on li.l_partkey = p.p_partkey<br>    -> inner join ( select l_partkey, 0.2 * avg(l_quantity) as quantity from lineitem use index (li_pkey_quan_idx) group by l_partkey ) as quantities<br>    -> on li.l_partkey = quantities.l_partkey and li.l_quantity < quantities.quantity<br>    -> where p.p_brand = 'Brand#33' and p.p_container = 'WRAP PACK';<br>+---------------+<br>| avg_yearly    |<br>+---------------+<br>| 312967.325714 | <br>+---------------+<br>1 row in set (12.38 sec)<br>

mysql> select sum(li.l_extendedprice) / 7.0 as avg_yearly from lineitem li use index (li_pkey_quan_idx) -> inner join part p on li.l_partkey = p.p_partkey -> inner join ( select l_partkey, 0.2 * avg(l_quantity) as quantity from lineitem use index (li_pkey_quan_idx) group by l_partkey ) as quantities -> on li.l_partkey = quantities.l_partkey and li.l_quantity < quantities.quantity -> where p.p_brand = 'Brand#33' and p.p_container = 'WRAP PACK'; +---------------+ | avg_yearly | +---------------+ | 312967.325714 | +---------------+ 1 row in set (12.38 sec)

Now we’re talking. 12.38s vs. 155.83s (12x improvement). I’m sure this is closer to what Jay was hoping for. Emboldened, I ran the modified query against the SF = 10G database:

<br>mysql> use tpch10G_tokudb;<br>Reading table information for completion of table and column names<br>You can turn off this feature to get a quicker startup with -A<br><br>Database changed<br>mysql> explain select sum(li.l_extendedprice) / 7.0 as avg_yearly from lineitem li use index (li_pkey_quan_idx)<br>    -> inner join part p on li.l_partkey = p.p_partkey<br>    -> inner join ( select l_partkey, 0.2 * avg(l_quantity) as quantity from lineitem use index (li_pkey_quan_idx) group by l_partkey ) as quantities<br>    -> on li.l_partkey = quantities.l_partkey and li.l_quantity < quantities.quantity<br>    -> where p.p_brand = 'Brand#33' and p.p_container = 'WRAP PACK';<br>+----+-------------+------------+--------+------------------+------------------+---------+----------------------+----------+-------------+<br>| id | select_type | table      | type   | possible_keys    | key              | key_len | ref                  | rows     | Extra       |<br>+----+-------------+------------+--------+------------------+------------------+---------+----------------------+----------+-------------+<br>|  1 | PRIMARY     | derived2   | ALL    | NULL             | NULL             | NULL    | NULL                 |  2000000 |             | <br>|  1 | PRIMARY     | p          | eq_ref | PRIMARY          | PRIMARY          | 4       | quantities.l_partkey |        1 | Using where | <br>|  1 | PRIMARY     | li         | ref    | li_pkey_quan_idx | li_pkey_quan_idx | 4       | quantities.l_partkey |   599860 | Using where | <br>|  2 | DERIVED     | lineitem   | index  | NULL             | li_pkey_quan_idx | 11      | NULL                 | 59986052 | Using index | <br>+----+-------------+------------+--------+------------------+------------------+---------+----------------------+----------+-------------+<br>4 rows in set (57.61 sec)<br><br>mysql> select sum(li.l_extendedprice) / 7.0 as avg_yearly from lineitem li use index (li_pkey_quan_idx)<br>    -> inner join part p on li.l_partkey = p.p_partkey<br>    -> inner join ( select l_partkey, 0.2 * avg(l_quantity) as quantity from lineitem use index (li_pkey_quan_idx) group by l_partkey ) as quantities<br>    -> on li.l_partkey = quantities.l_partkey and li.l_quantity < quantities.quantity<br>    -> where p.p_brand = 'Brand#33' and p.p_container = 'WRAP PACK';<br>+----------------+<br>| avg_yearly     |<br>+----------------+<br>| 3253728.844286 | <br>+----------------+<br>1 row in set (1 hour 7 min 8.55 sec)<br>

So Jay’s ideas offer a solution that does not involve clustering keys and produces a result in a manageable timeframe. However, relative to clustering keys, it is still 4028 / 101 = 40x slower.