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.
add indexes for every column that is involved in the query. it will be MUCH faster!!
campaigntrack.albumid campaigntrack.title searchresult.title
-jsd-
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
