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