Dear all,
I have a table with 2 millon records and 8 columns. None of the column is uniq but each row is uniq.

I have a query (plz refer my last post 'how to select') which is a bit complex and sometimes takes more than 5 hours finish the job. I have created index on the most referrd columns seperately and also together. But none of this solved the problem.


The EXPLAIN says...

mysql> explain select A.*, count(distinct B.ls) as lss from dsdbase A left join dsdbase B on (A.code=B.code and B.ls in (37,14,38,32)) group by A.code, A.ls, A.no1, A.no2 having lss>=3 and A.ls in (37,14,38,32) order by A.code;

+-------+------+-----------------------+--------+---------+--------+---------+----------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+-----------------------+--------+---------+--------+---------+----------------+
| A | ALL | NULL | NULL | NULL | NULL | 2385617 | Using filesort |
| B | ref | cindex,lindex,lcindex | cindex | 4 | A.code | 122 | Using where |
+-------+------+-----------------------+--------+---------+--------+---------+----------------+
2 rows in set (0.34 sec)

How can I solve this problem.

Thanks in advance.