DBT3 Benchmark Queries

Known things about DBT-3 benchmark and its queries

Q1

A simple, one-table query.

select
        l_returnflag,
        l_linestatus,
        sum(l_quantity) as sum_qty,
        sum(l_extendedprice) as sum_base_price,
        sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
        sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
        avg(l_quantity) as avg_qty,
        avg(l_extendedprice) as avg_price,
        avg(l_discount) as avg_disc,
        count(*) as count_order
from
        lineitem
where
        l_shipdate <= date_sub('1998-12-01', interval 79 day)
group by
        l_returnflag,
        l_linestatus
order by
        l_returnflag,
        l_linestatus;

Query plan:

+------+-------------+----------+------+---------------+------+---------+------+----------+----------------------------------------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows     | Extra                                        |
+------+-------------+----------+------+---------------+------+---------+------+----------+----------------------------------------------+
|    1 | SIMPLE      | lineitem | ALL  | i_l_shipdate  | NULL | NULL    | NULL | 59711977 | Using where; Using temporary; Using filesort |
+------+-------------+----------+------+---------------+------+---------+------+----------+----------------------------------------------+

• l_shipdate < date_sub('1998-12-01', interval 79 day) is satisifed by 59,334,576 rows.
• The table has 59,986,052 rows in total.
• There are a total of 4 different values of (l_returnflag,l_linestatus). This means, sorting doesn't matter, and temporary table is a very small heap table.

Q3

select
       l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue,
       o_orderdate, o_shippriority
from customer, orders, lineitem
where c_mktsegment = 'BUILDING' and c_custkey = o_custkey
      and l_orderkey = o_orderkey and o_orderdate < date '1995-03-15'
      and l_shipdate > date '1995-03-15'
group by l_orderkey, o_orderdate, o_shippriority
order by revenue desc, o_orderdate
limit 10;

There seems to be an improvement in mysql-5.6: http://jorgenloland.blogspot.ru/2013/02/dbt-3-q3-6-x-performance-in-mysql-5610.html

(speedup can be observed only when the query is in the form like the above (TODO: figure out where do different forms of queries come from?))

EXPLAINs (scale=10):

+------+-------------+----------+--------+---------------------------------------------------------+---------+---------+----------------------------+----------+----------------------------------------------+
| id   | select_type | table    | type   | possible_keys                                           | key     | key_len | ref                        | rows     | Extra                                        |
+------+-------------+----------+--------+---------------------------------------------------------+---------+---------+----------------------------+----------+----------------------------------------------+
|    1 | SIMPLE      | orders   | ALL    | NULL                                                    | NULL    | NULL    | NULL                       | 15115145 | Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | customer | eq_ref | PRIMARY                                                 | PRIMARY | 4       | dbt3sf10.orders.o_custkey  |        1 | Using where                                  |
|    1 | SIMPLE      | lineitem | ref    | PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4       | dbt3sf10.orders.o_orderkey |        2 | Using where                                  |
+------+-------------+----------+--------+---------------------------------------------------------+---------+---------+----------------------------+----------+----------------------------------------------+

+------+-------------+----------+--------+---------------------------------------------------------+----------------+---------+----------------------------+---------+---------------------------------------------------------------------+
| id   | select_type | table    | type   | possible_keys                                           | key            | key_len | ref                        | rows    | Extra                                                               |
+------+-------------+----------+--------+---------------------------------------------------------+----------------+---------+----------------------------+---------+---------------------------------------------------------------------+
|    1 | SIMPLE      | orders   | range  | PRIMARY,i_o_date_clerk                                  | i_o_date_clerk | 4       | NULL                       | 7557572 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | customer | eq_ref | PRIMARY                                                 | PRIMARY        | 4       | dbt3sf10.orders.o_custkey  |       1 | Using where                                                         |
|    1 | SIMPLE      | lineitem | ref    | PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity | PRIMARY        | 4       | dbt3sf10.orders.o_orderkey |       2 | Using where                                                         |
+------+-------------+----------+--------+---------------------------------------------------------+----------------+---------+----------------------------+---------+---------------------------------------------------------------------+