On Thu, Aug 21, 2003 at 01:03:16AM +0000, gord barq wrote: > I have this query which does a left outer join and it takes forever (like > half a day). Here are the results of an explain analysis. > > mysql> explain SELECT count(searchresult.title) AS number, > campaigntrack.title, tracknum, trackid FROM campaigntrack LEFT OUTER JOIN > searchresult ON searchresult.title = campaigntrack.title WHERE > campaigntrack.albumid = 1 GROUP BY title ORDER BY tracknum; > +---------------+------+---------------+------+---------+------+----------+---------------------------------------------+ > | table | type | possible_keys | key | key_len | ref | rows | > Extra | > +---------------+------+---------------+------+---------+------+----------+---------------------------------------------+ > | campaigntrack | ALL | NULL | NULL | NULL | NULL | 62 | > where used; Using temporary; Using filesort | > | searchresult | ALL | NULL | NULL | NULL | NULL | 19875751 | > | > +---------------+------+---------------+------+---------+------+----------+---------------------------------------------+ > > Here are the schemas for the tables: > > campaignalbum: > > | trackid | int(10) unsigned | | PRI | NULL | auto_increment | > | tracknum | int(11) | YES | | NULL | | > | artistid | int(11) | YES | | NULL | | > | albumid | int(11) | YES | | NULL | | > | title | varchar(255) | YES | | NULL | | > > searchresult: > > | title | varchar(255) | YES | | NULL | | > > Any suggestions on how to optimize this would be greatly appreciated.
Index the title columns. At least in the searchresult table. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 19 days, processed 959,268,902 queries (567/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
