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