SEO

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,1237817100 24,123781710031,123781710033,123781710035,12378171 0067,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.