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 <[EMAIL PROTECTED]> 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* <[EMAIL PROTECTED] <mailto:
>> [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]
>> <mailto:[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] <mailto:[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
>> <http://yueliangdao0608.cublog.cn/>
>>
>>
>>
>>
