Le query del benchmark DBT3

Alcuni aspetti noti sul benchmark DBT-3 e sulle sue query.

Q1

Una query semplice, di una tabella.

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;

Piano della query:

+------+-------------+----------+------+---------------+------+---------+------+----------+----------------------------------------------+
| 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) è soddisfatta da 59,334,576 righe.
• La tabella ha 59,986,052 righe in totale.
• Vi è un totale di 4 differenti valori di (l_returnflag,l_linestatus). Questo significa che l'ordinamento non è importante e la tabella temporanea è una heap molto piccola.

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;

Sembra esserci un miglioramento in mysql-5.6: http://jorgenloland.blogspot.ru/2013/02/dbt-3-q3-6-x-performance-in-mysql-5610.html

(l'aumento di velocità si osserva solo se la query è in questa forma (TODO: capire da dove vengono le diverse forme delle query?))

EXPLAIN (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                                                         |
+------+-------------+----------+--------+---------------------------------------------------------+----------------+---------+----------------------------+---------+---------------------------------------------------------------------+