Getting Popular Topics on a Custom Made Forum
- by Navarr
For this website we're working on, we're trying to get the most popular topics (based on how many posts have been made in them within the last 24 hours). We have a medium to large based forum, and the current MySQL query looks like this:
SELECT `forums_topics`.*,
(
SELECT COUNT(`id`)
FROM `forums_posts`
WHERE `postdate` > (UNIX_TIMESTAMP()-60*60*24)
AND `topicid`=`forums_topics`.`id`
) AS `trendy_threads`
FROM `forums_topics`
WHERE `deleted`=0
ORDER BY `trendy_threads` DESC,`postdate` DESC
LIMIT 3
This unfortunately causes an SQL timeout with just one query and Error 500's.
How can we get this information as quickly and as efficiently as possible?
