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