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