Hi Stephen,

>>>> 2012/04/10 15:58 -0400, Stephen Tu >>>>
>| id | select_type | table | type | possible_keys | key
>| key_len | ref | rows |
>Extra |
>+----+-------------+--------------+--------+-------------------+-----------+---------+-------------------------------------+---------+-----------------------------------------------------+
>| 1 | SIMPLE | CUSTOMER_INT | ALL | PRIMARY | NULL
>| NULL | NULL | 1501528 | Using
>temporary; Using filesort |
>| 1 | SIMPLE | NATION_INT | eq_ref | PRIMARY | PRIMARY
>| 4 | tpch-10.00.CUSTOMER_INT.C_NATIONKEY | 1 |
>NULL |
>| 1 | SIMPLE | ORDERS_INT | ref | PRIMARY,O_CUSTKEY | O_CUSTKEY
>| 4 | tpch-10.00.CUSTOMER_INT.C_CUSTKEY | 7 | Using where;
>Using join buffer (Batched Key Access) |
>| 1 | SIMPLE | LINEITEM_INT | ref | PRIMARY | PRIMARY
>| 4 | tpch-10.00.ORDERS_INT.O_ORDERKEY | 1 | Using
>where |
>+----+-------------+--------------+--------+-------------------+-----------+---------+-------------------------------------+---------+-----------------------------------------------------+
>4 rows in set (0.00 sec)
>
>I'm wondering why, in this particular query, mysql doesn't use the BKA to
>join the LINEITEM_INT table, but uses it for the ORDERS_INT table? It
seems
>like it should also use BKA to batch key fetches from the LINEITEM_INT
>table (this I believe is the reason for the slow performance).
<<<<<<<<

The basis for Batched Key Access (BKA) is the Disk-Sweep Multi-Range
Read (DS-MRR) strategy. The basic idea of DS-MRR is to accumulate
primary keys from a batch of secondary index look-ups and access the
rows in the base table in primary key order. In other words, DS-MRR
(and BKA) does not apply for look-ups by primary key. Hence, since
the ref access into lineitem is by primary key, BKA will not be used.

Maybe you will get a more optimal plan if you add more indexes. In my
case, where I have an index on orders(o_orderdate), the join will
start with a range scan on the orders tables. If I enable DS-MRR for
this range scan, query time is reduced from 455 seconds (without
DS-MRR) to 90 seconds on a scale 1 database.

Hope this helps,

--
?ystein

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql