Need help on optimizing the select statement: Table structure
table1 ------ id p_id table2 ------ p_id out_id table3 ------ out_id name_id table4 ------- name_id (unique) prev_id start_id (unique) end_id (unique) Only table4 has unique fields all other fields are not unique. The following select statement does what is required but the tables are really huge (> 50 million records) so need to be optimized SELECT DISTINCT (a.id) FROM table1 a, table1 b, table2 c, table3 d, table4 e, table4 f WHERE a.id =275 AND a.p_id = b.p_id AND a.id != b.id AND a.p_id = c.p_id AND c.out_id = d.out_id AND d.name_id = e.name_id AND e.start_id >= f.start_id AND e.end_id <= f.end_id AND e.end_id !=0 AND (f.name_id =45 OR f.name_id =56) GROUP BY b.id The explain for the above statement is a | ref | id,p_id,combine | combine | 4 | const | 5281 | Using where; Using index; Using temporary; Using f... c | eq_ref | PRIMARY,p_id | PRIMARY | 4 | a.p_id | 1 | | | d | ref | name_id,out_id | out_id | 4 | b.out_id | 1 | | | b | ref | p_id | p_id | 4 | a.p_id | 3 | Using where | e | range | PRIMARY,start_id,end_id,combine,name_id | PRIMARY | 4 | NULL | 2 | Using where | f | eq_ref | PRIMARY,start_id,end_id,combine,name_id | PRIMARY | 4 | c.name_id | 1 | Using where | I am sure there should be some better way to do this using Inner join or something similar but I am not sure how. It will be helpful if you could suggest me some improvements for this query. If you need any further explanation please let me know. Thanks for your help Karu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
