Getting Popular Topics on a Custom Made Forum

Question

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`.`id`,`forums_topics`.`name`,
    (
        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
    AND `lastpost` > (UNIX_TIMESTAMP()-60*60*24)
    ORDER BY `trendy_threads` DESC,`postdate` DESC
    LIMIT 3

The SQL is quite sluggish.

How can we get this information as quickly and as efficiently as possible?

forums_topics

Field   Type    Null    Key Default Extra
id  int(50) NO  PRI NULL    auto_increment
uid varchar(255)    NO      NULL     
flag    int(1)  NO      0    
boardid varchar(255)    NO      NULL     
postdate    varchar(255)    NO      NULL     
lastpost    bigint(255) NO      NULL     
name    varchar(50) NO      NULL     
description text    NO      NULL     
body    text    NO      NULL     
author  varchar(25) NO      NULL     
deleted tinyint(3) unsigned NO      0    
deletememberid  int(10) unsigned    NO      0    
pinned  tinyint(1)  NO      0    
flagged text    NO      NULL     
privateaccess   text    NO      NULL     
lastposter  int(255)    NO      1    
replycount  int(255)    NO      0    
viewcount   int(255)    NO      0    
movedfrom   int(255)    NO      0     

forums_posts

Field   Type    Null    Key Default Extra
id  int(50) NO  PRI NULL    auto_increment
topicid int(10) unsigned    NO      0    
author  varchar(25) NO      NULL     
postdate    varchar(255)    NO      NULL     
body    text    NO      NULL     
lastedit    varchar(255)    NO      NULL     
postcount   tinyint(1)  NO      NULL     
invincible  tinyint(1)  NO      0    
deleted tinyint(3) unsigned NO      0    
deletememberid  int(10) unsigned    NO      0    
thumbsup    int(255)    NO      0    
thumbsdown  int(255)    NO      0    
thumbsupuser    text    NO      NULL     
thumbsdownuser  text    NO      NULL     

Answer 1

I'm going to take a stab in the dark, and I'll edit further if needed. An EXPLAIN query would help.

SELECT `forums_topics`.*
FROM (
    SELECT `topicid`, COUNT(*) as num
    FROM `forums_posts` 
    WHERE `postdate` > (UNIX_TIMESTAMP()-60*60*24) 
    GROUP BY `topicid`
    ORDER BY num DESC, `postdate` DESC
    LIMIT 3
) `trendy`
LEFT JOIN `forums_topics` ON `id`=`topicid`
WHERE `deleted`=0

Answer 2

The problem is probably that MySQL evaluates the subquery for every row. You can give MySQL a hint that that it should execute the subquery only once by moving the subquery into a join:

SELECT  *
FROM    forum_topics ft
JOIN    (
            SELECT  topicid
            ,       COUNT(*) as cnt
            FROM    forums_posts
            WHERE   postdate > UNIX_TIMESTAMP()-60*60*24
            GROUP BY 
                    topicid
        ) fpc
ON      ft.topicid = fpc.topicid
WHERE   ft.deleted = 0
ORDER BY 
        fpc.cnt DESC
,       ft.postdate DESC
LIMIT 3

An index on forum_posts(postdate, topicid) would further improve performance.