lists.zerezo.com
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
***BOGO*** Re: improve performance of this sql
- Date: Thu, 26 Jun 2008 18:12:29 +0530
- From: "Ananda Kumar" <anandkl@xxxxxxxxx>
- Subject: ***BOGO*** Re: improve performance of this sql
Hi All,
I just spoke to the dev, they say this is the main sql
select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE, A.IS_NULL,
A.HEIGHT,
A.NO_LISTINGS, A.NO_SUCC_LISTINGS, A.TOTAL_QTY, A.SOLD_QTY, A.ASP,
A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE, A.MIN_ITEM_ID,
A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP C where B.KEYWORDS in (?) and
B.KR_ID = C.KR_ID and
C.CLUSTER_ID = A.CLUSTER_ID order by case A.IS_NULL when 'Y' then 2 when
'L' then 1 else 0 end desc, A.NO_LISTINGS desc)
A limit 40
Thats the reason they use derived table. So any tips on tuning this sql
On 6/23/08, Ananda Kumar <anandkl@xxxxxxxxx> wrote:
>
> yes, there could be different cluster_id's for the same kr_id.
>
> On 6/23/08, Jocelyn Fournier <joc@xxxxxxxxxxxxxxx> wrote:
>>
>> Ok, so just to be sure, in C, kr_id is not unique by design (you have
>> several different cluster_id for the same kr_id) ?
>>
>>
>> Ananda Kumar a écrit :
>>
>>> B has single column KR_ID as primary key, where as C has combined primary
>>> key (kr_id,cluster_id) and data type on both tables for KR_ID are same.
>>>
>>> On 6/23/08, *Jocelyn Fournier* <joc@xxxxxxxxxxxxxxx <mailto:
>>> joc@xxxxxxxxxxxxxxx>> wrote:
>>>
>>> Hi,
>>>
>>> Oops, indeed, C is a primary key :)
>>> But what's weird is MySQL is using a ref type for the join between B
>>> and C, and not an eq_ref.
>>> Could you check B.KR_ID and C.KR_ID are of the same data type ?
>>>
>>> Regards,
>>> Jocelyn
>>>
>>> Ananda Kumar a écrit :
>>>
>>> Hi Jo,
>>> Yes there is a combined index on (keywords,kr_id) on B,
>>> c.kr_id is a primary key.
>>> Let me talk to my dev and check why they are using derived.
>>> Thanks for noticing this.
>>>
>>> On 6/23/08, *Jocelyn Fournier* <joc@xxxxxxxxxxxxxxx
>>> <mailto:joc@xxxxxxxxxxxxxxx> <mailto:joc@xxxxxxxxxxxxxxx
>>> <mailto:joc@xxxxxxxxxxxxxxx>>> wrote:
>>>
>>> Hi,
>>>
>>> AFAIK, to optimize your query you should have :
>>>
>>> 1 index on B.KEYWORDS. (I assume it's the
>>> KD_KW_KI_IDX_0806120615
>>> index ?)
>>> 1 index on C.KR_ID. Your index is not UNIQUE here, is this
>>> expected ?
>>> 1 index unique on A.CLUSTER_ID (it's already the case)
>>>
>>> BTW, why are you using a derived table here ?
>>> You could write directly :
>>>
>>> select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
>>> A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
>>> A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE,
>>> A.MAX_PRICE,
>>> A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B, ER_MAP
>>> C where
>>> B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and C.CLUSTER_ID
>>> =
>>> A.CLUSTER_ID limit 40
>>>
>>> Regards,
>>> Jocelyn Fournier
>>>
>>>
>>> Ananda Kumar a écrit :
>>>
>>> explain select * from (select A.LEAF_CATEG_ID,
>>> A.CLUSTER_ID,
>>> A.SIGNATURE,
>>> A.IS_NULL, A.HEIGHT, A.NO_LISTINGS, A.NO_SUCC_LISTINGS,
>>> A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE,
>>> A.MAX_PRICE,
>>> A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A, R_DATA B,
>>> ER_MAP C
>>> where
>>> B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and
>>> C.CLUSTER_ID =
>>> A.CLUSTER_ID ) A limit 40\G;
>>> *************************** 1. row
>>> ***************************
>>> id: 1
>>> select_type: PRIMARY
>>> table: <derived2>
>>> type: ALL
>>> possible_keys: NULL
>>> key: NULL
>>> key_len: NULL
>>> ref: NULL
>>> rows: 3231
>>> Extra:
>>> *************************** 2. row
>>> ***************************
>>> id: 2
>>> select_type: DERIVED
>>> table: B
>>> type: ref
>>> possible_keys: PRIMARY,KD_KW_KI_IDX_0806120615
>>> key: KD_KW_KI_IDX_0806120615
>>> key_len: 767
>>> ref:
>>> rows: 720
>>> Extra: Using where; Using index
>>> *************************** 3. row
>>> ***************************
>>> id: 2
>>> select_type: DERIVED
>>> table: C
>>> type: ref
>>> possible_keys: PRIMARY
>>> key: PRIMARY
>>> key_len: 10
>>> ref: reh.B.kr_id
>>> rows: 1
>>> Extra: Using index
>>> *************************** 4. row
>>> ***************************
>>> id: 2
>>> select_type: DERIVED
>>> table: A
>>> type: eq_ref
>>> possible_keys: PRIMARY
>>> key: PRIMARY
>>> key_len: 10
>>> ref: reh.C.cluster_id
>>> rows: 1
>>> Extra:
>>> 4 rows in set (0.03 sec)
>>>
>>> ERROR:
>>> No query specified
>>>
>>>
>>> On 6/22/08, Moon's Father <yueliangdao0608@xxxxxxxxx
>>> <mailto:yueliangdao0608@xxxxxxxxx>
>>> <mailto:yueliangdao0608@xxxxxxxxx
>>> <mailto:yueliangdao0608@xxxxxxxxx>>> wrote:
>>>
>>> Can you show us the proper view of your sql statement
>>> by
>>> adding \G.
>>>
>>> On Sat, Jun 21, 2008 at 1:57 PM, Ananda Kumar
>>> <anandkl@xxxxxxxxx <mailto:anandkl@xxxxxxxxx>
>>> <mailto:anandkl@xxxxxxxxx <mailto:anandkl@xxxxxxxxx>>> wrote:
>>>
>>> The below query performance in 10 sec when there
>>> are no
>>> other activity on
>>> db
>>> , but when any insert or LOAD DATA Index creation
>>> happens it takes close
>>> to
>>> 80 sec. Any ways to improve the performance of
>>> this sql.
>>>
>>> innodb_buffer=11GB , key_buffer=3 GB, we have
>>> totally 16GB
>>>
>>>
>>> EXPLAIN select * from (select A.LEAF_CATEG_ID,
>>> A.CLUSTER_ID, A.SIGNATURE,
>>> A.IS_NULL, A.HEIGHT, A.NO_LISTINGS,
>>>
>>> A.NO_SUCC_LISTINGS,
>>> A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS,
>>> A.MIN_PRICE,
>>> A.MAX_PRICE,
>>> A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A,
>>>
>>> R_DATA B, ER_MAP C where B.KEYWORDS IN
>>> ('CAMERA') and
>>> B.KR_ID = C.KR_ID
>>> and
>>> C.CLUSTER_ID = A.CLUSTER_ID ) A limit
>>>
>>> 40;
>>>
>>>
>>> +----+-------------+------------+--------+---------------------------------+-------------------------+---------+-------------
>>>
>>> ----------+------+--------------------------+
>>> | id | select_type | table | type |
>>> possible_keys
>>> |
>>> key | key_len | ref
>>>
>>> | rows | Extra |
>>>
>>>
>>> +----+-------------+------------+--------+---------------------------------+-------------------------+---------+-------------
>>>
>>> ----------+------+--------------------------+
>>> | 1 | PRIMARY | <derived2> | ALL | NULL
>>> |
>>> NULL | NULL | NULL
>>>
>>> | 3278 | |
>>> | 2 | DERIVED | B | ref |
>>> PRIMARY,KD_KW_KI_IDX_0805230323
>>> |
>>> KD_KW_KI_IDX_0805230323 | 767 |
>>>
>>> | 1524 | Using where; Using index |
>>> | 2 | DERIVED | C | ref | PRIMARY
>>> |
>>> PRIMARY | 10 |
>>>
>>> reh.B.kr_id | 1 | Using index
>>> |
>>> | 2 | DERIVED | A | eq_ref | PRIMARY
>>> |
>>> PRIMARY | 10 |
>>>
>>> reh.C.cluster_id | 1 |
>>> |
>>>
>>>
>>> +----+-------------+------------+--------+---------------------------------+-------------------------+---------+-------------
>>>
>>> ----------+------+--------------------------+
>>>
>>>
>>>
>>> --
>>> I'm a MySQL DBA in china.
>>> More about me just visit here:
>>> http://yueliangdao0608.cublog.cn
>>> <http://yueliangdao0608.cublog.cn/>
>>> <http://yueliangdao0608.cublog.cn/>
>>>
>>>
>>>
>>>
>>>
>