Can you send us a new explain on this version? Donny
> -----Original Message----- > From: karthik viswanathan [mailto:[EMAIL PROTECTED] > Sent: Sunday, February 29, 2004 4:12 PM > To: [EMAIL PROTECTED] > Subject: Re: optimizing a select statement over a database with >50 > million recs > > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
