From: Rick James
Date: April 17 2012 6:57pm
Subject: RE: forcing mysql to use batched key access (BKA) optimization  for
 joins
List-Archive: http://lists.mysql.com/mysql/227208
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB1485A7D534@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

INDEX(o_orderdate, o_cust_key, o_orderkey)
Would probably help a bunch.

I assume you have indexes (PKs?) on c_custkey, l_order_key, n_nationkey.

Please provide SHOW CREATE TABLE and SHOW STATUS TABLE.

-----Original Message-----
From: Hal?sz S?ndor [mailto:hsv@stripped]=20
Sent: Tuesday, April 10, 2012 5:20 PM
To: mysql@stripped
Subject: Re: forcing mysql to use batched key access (BKA) optimization for=
 joins

>>>> 2012/04/10 15:58 -0400, Stephen Tu >>>>
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 =3D o_custkey
  and l_orderkey =3D o_orderkey
  and o_orderdate >=3D date '1994-08-01'
  and o_orderdate < date '1994-08-01' + interval '3' month
  and l_returnflag =3D 'R'   and c_nationkey =3D n_nationkey
group by   c_custkey,   c_name,   c_acctbal,   c_phone,   n_name,
c_address,   c_comment
order by   revenue desc limit 20
<<<<<<<<
I understand that MySQL works better if inner joining explicitly is stated,=
 not implicitly as you have it.

What are your keys, indices? Going by that which I have read & heard, you w=
ant every field named after this query s 'where' an index, if not key--and,=
 of course, your every field named '...key' is a key, right?


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