hi,
i need help optimizing this query. here’s the query i’m using right now:
SELECT f.feed_id, f.feed_title, COUNT(a.article_id) as numUnread
FROM feedroll fr LEFT JOIN articles a ON a.feed_id = fr.feed_id AND a.found_date > fr.lastVisited_date LEFT JOIN feeds f ON f.feed_id = fr.feed_id
WHERE fr.member_id = 1
GROUP BY fr.feed_id
ORDER BY f.feed_title
this is for a news reader. it basically pulls all the feeds that a specific member is subscribed to and counts the number of unread articles for each feed. the above query works but i just tested it on a member and it took 72 seconds. not good. i’ve attached the explain statement. i have all the needed keys in place.
so what can i do to make this run faster? how can i rearange the different parts of the query? it is obvious that it’s possible to speed thing up as there are many news readers out there with this functionality. there are 120,000 articles in the article table.
here’s the explain statement:
http://forums.devshed.com/attachment.php?attachmentid=8606