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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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
>>>
>>
>>
