From: Stephen Tu Date: April 10 2012 7:58pm Subject: forcing mysql to use batched key access (BKA) optimization for joins List-Archive: http://lists.mysql.com/mysql/227163 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=20cf303ea768d1253d04bd588c26 --20cf303ea768d1253d04bd588c26 Content-Type: text/plain; charset=ISO-8859-1 Hi all, I am playing around w/ the BKA optimization found in mysql 5.6. I'm having an issue getting the expected increase in performance with this optimization. Specifically, I'm trying to optimize the following query (TPC-H Q10): select c_custkey, c_name, sum(l_extendedprice * (100 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from CUSTOMER_INT, ORDERS_INT, LINEITEM_INT, NATION_INT where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date '1994-08-01' and o_orderdate < date '1994-08-01' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc limit 20; When running the query, iostat reports the disk reading at about 4MB/s, which suggests lots of random seeking. Looking at explain, I get: mysql> explain select c_custkey, c_name, sum(l_extendedprice * (100 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from CUSTOMER_INT, ORDERS_INT, LINEITEM_INT, NATION_INT where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date '1994-08-01' and o_orderdate < date '1994-08-01' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc limit 20; +----+-------------+--------------+--------+-------------------+-----------+---------+-------------------------------------+---------+-----------------------------------------------------+ | 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). Relative mysql paramters: --innodb-buffer-pool-size=2G --optimizer_switch=mrr=on --optimizer_switch=mrr_cost_based=off --optimizer_switch=batched_key_access=on --join_buffer_size=12M --read_rnd_buffer_size=12M Hopefully, I'm not missing anything too obvious here. Thanks! --20cf303ea768d1253d04bd588c26--