List:General Discussion« Previous MessageNext Message »
From:(Hal Date:April 11 2012 12:19am
Subject:Re: forcing mysql to use batched key access (BKA) optimization
for joins
View as plain text  
>>>> 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 = 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
<<<<<<<<
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 want 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?

Thread
forcing mysql to use batched key access (BKA) optimization for joinsStephen Tu10 Apr
  • Re: forcing mysql to use batched key access (BKA) optimizationfor joinshsv11 Apr
    • RE: forcing mysql to use batched key access (BKA) optimization forjoinsRick James17 Apr