Hi All, I am using mysql 5.0.41, on debian, with 8 CPU, 8 GB RAM. I am having the below query with explain plan
select * from (select * from (select truncate((((0.34*LOG(10,((NO_BIDS + 1)/(TOTAL_QTY + 1)))) + (0.24*LOG(10,100*( (SOLD_QTY + 1) / (TOTAL_QTY + 1)))) + 0.14*(1 / LOG(10, 10 + TOTAL_QTY / NO_SLRS))) * 100),2) as CLUSTER_RANK, LEAF_CATEG_ID, CD.CLUSTER_ID, SIGNATURE, NO_LISTINGS, NO_SUCC_LISTINGS, ASP, NO_BIDS, MIN_PRICE, MAX_PRICE, NO_SLRS, TOTAL_QTY, SOLD_QTY from Cluster_data CD where CD.cluster_id in ( select B2.child_cluster_id from cluster_hierarchy B2 LEFT JOIN cluster_hierarchy B1 ON B2.child_cluster_id=B1.cluster_id where B1.cluster_id IS NULL and B2.child_cluster_id in (123781710012,123781710015,123781710023,123781710024,123781710031,123781710033,123781710035,123781710067,123781710067,123781710069)) LIMIT 500 )X order by 1 DESC ) Y where CLUSTER_RANK > 29 LIMIT 30 +----+--------------------+-------+------+-------------------------+-------------------------+---------+------------------------------+-------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+-------------------------+-------------------------+---------+------------------------------+-------+-----------------------------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table | | 3 | DERIVED | CD | ALL | NULL | NULL | NULL | NULL | 12752 | Using where | | 4 | DEPENDENT SUBQUERY | B2 | ref | CH_CCI_IDX_0803120214 | CH_CCI_IDX_0803120214 | 10 | func | 3 | Using where; Using index | | 4 | DEPENDENT SUBQUERY | B1 | ref | KD_KW_KI_IDX_0803120214 | KD_KW_KI_IDX_0803120214 | 10 | research.B2.child_cluster_id | 4 | Using where; Using index; Not exists | +----+--------------------+-------+------+-------------------------+-------------------------+---------+------------------------------+-------+-----------------------------------------------------+ mysql> show index from cluster_data; +--------------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | cluster_data | 0 | PRIMARY | 1 | cluster_id | A | 12752 | NULL | NULL | | BTREE | | | cluster_data | 1 | CD_LCI_IDX_0803120214 | 1 | leaf_categ_id | A | 2 | NULL | NULL | | BTREE | | +--------------+------------+-----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ Is there any options for me to tune this query. Here in the test envirionment CD table has only 12752 rows, but actually in productin it will have close to 8 Million rows. So with the above explain plan, the query would take too much time as its reading FULL TABLE on CD. Can you please help me in tuning this query. Also the "IN" clause will have close to 1000 values at any time.
