here is the updated select statement i came with but still needs improvement
SELECT Distinct (a.id) >From table1 a INNER JOIN table1 b USING ( p_id ) INNER JOIN table2 c USING ( p_id ) INNER JOIN table3 d USING ( out_id ) INNER JOIN table4 e ON ( d.name_id = e.name_id ) INNER JOIN table4 f ON ( e.start_id BETWEEN f.left_id AND f.end_id ) WHERE (f.name_id =45 OR f.name_id =56) AND b.id =275 AND a.id != b.id This reduced the time from 0.5 secs to 0.3 secs but still i am looking for some major improvemnts. I am using this query in PHP and its been repeated several times to display a list. Any advice on this will be really useful. Is there anything I could do with the mysql and php configuration to increase the performance? The server has just 1GB ram, will adding more memory help? Thanks Karthik. > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
