subject:"Re\: improve performance of this sql"

Re: improve performance of this sql

2008-06-26 Thread Ananda Kumar

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] >> [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]
>>> >>>> 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: 
>>>   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
>>>***
>>>

Re: improve performance of this sql

2008-06-23 Thread Ananda Kumar

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] > [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]
>> >>> 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: 
>>   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
>>

Re: improve performance of this sql

2008-06-23 Thread Jocelyn Fournier

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] 
> 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]
 >> 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: 
   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

Re: improve performance of this sql

2008-06-23 Thread Ananda Kumar

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] > [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: 
>>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;
>>
>>
>>  
>> ++

Re: improve performance of this sql

2008-06-23 Thread Jocelyn Fournier

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] 
> 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: 
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|

++-+++-

Re: improve performance of this sql

2008-06-23 Thread Ananda Kumar

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: 
>> 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 |  | 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 |  |


 ++-+++-+-+-+-

 -

Re: improve performance of this sql

2008-06-23 Thread Jocelyn Fournier


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: 
 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 |  | 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




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: improve performance of this sql

2008-06-22 Thread Ananda Kumar

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: 
 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 |  | 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

Re: improve performance of this sql

2008-06-21 Thread Moon's Father

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 |  | 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

Re: improve performance of this sql

Re: improve performance of this sql

Re: improve performance of this sql

Re: improve performance of this sql

Re: improve performance of this sql

Re: improve performance of this sql

Re: improve performance of this sql

Re: improve performance of this sql

Re: improve performance of this sql

9 matches

Site Navigation

Mail list logo

Footer information