[prev in list] [next in list] [prev in thread] [next in thread]
List: mysql
Subject: RE: forcing mysql to use batched key access (BKA) optimization for joins
From: Rick James <rjames () yahoo-inc ! com>
Date: 2012-04-17 18:57:03
Message-ID: 2E7DD7ADE53B044C8C8BCD9C5829E1EB1485A7D534 () SP2-EX07VS01 ! ds ! corp ! yahoo ! com
[Download RAW message or body]
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@tbbs.net]
Sent: Tuesday, April 10, 2012 5:20 PM
To: mysql@lists.mysql.com
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 = 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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic