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.
Thanks.
__________________________________________________ _______________
<b>Get MSN 8</b> and enjoy automatic e-mail virus protection.
http://join.msn.com/?page=features/virus
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw