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,
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 colum
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.
O
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
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,k
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
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
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.
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
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 (sel
10 matches
Mail list logo
