yes, there could be different cluster_id's for the same kr_id.
On 6/23/08, Jocelyn Fournier <[EMAIL PROTECTED]> 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* <[EMAIL PROTECTED] <mailto: >> [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]> <mailto:[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]> >> <mailto:[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]> >> <mailto:[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/> >> <http://yueliangdao0608.cublog.cn/> >> >> >> >> >>
