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 <anandkl@stripped> 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
