extern.php query

I've brought this up before, but I think I've finally tracked down all the last nagging problems. Basically, issues arise with a larger forum and the extern.php file. The RSS feeds hadn't been a huge issue because not all that many people use them, and they weren't getting hit constantly. However, when I went to place a listing of active topics on our front website, including extern.php would prevent the page from loading for 10-20 seconds, and it would eventually stall the entire server.

Here's a basic query for reference:

SELECT t.id, t.subject
    FROM topics AS t
        INNER JOIN forums AS f ON f.id=t.forum_id
        LEFT JOIN forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id=3)
    WHERE (fp.read_forum IS NULL OR fp.read_forum=1)
        AND t.moved_to IS NULL
        AND f.id IN(1,2,3,4,5)
    ORDER BY t.last_post DESC
    LIMIT 15

The first problem with this is that with punbb's default indexes, the query is using the index placed on the moved_to column. This makes sense, but when it comes to sorting the results by either the last_post or posted columns, things take a very long time.

My first instinct was to place a multi column index with both moved_to and last_post. However, the problem with this, is that if you sort by the posted column, that index won't work (or vice-versa). And simply creating another multi-column index won't work either, since it picks the first index it finds that contains moved_to as the first column.

After a bit of head-banging, I found the solution is to join the topics table onto itself, so no multi-column indexes are needed.

With that taken care of, and indexes placed on both the last_post  and posted columns, queries without a restriction on the forums perform quite nicely. However, as soon as you throw in a where clause on the forums, things break down once again. The solution is to change the forum's join from an inner to a left join.

So all in all, here are the changes I made to extern.php...

The $order_by variable becomes:

$order_by = ($_GET['action'] == 'active') ? 't2.last_post' : 't2.posted';

The RSS query becomes:

$result = $db->query('SELECT t.id, t.poster, t.subject, t.posted, t.last_post, f.id AS fid, f.forum_name FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'topics AS t2 ON t.id = t2.id LEFT JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id=3) WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.moved_to IS NULL'.$forum_sql.' ORDER BY '.$order_by.' DESC LIMIT 15') or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());

The HTML query becomes:

$result = $db->query('SELECT t.id, t.subject FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'topics AS t2 ON t.id = t2.id LEFT JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id=3) WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.moved_to IS NULL'.$forum_sql.' ORDER BY '.$order_by.' DESC LIMIT '.$show) or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());

And then the following indexes added:

ALTER TABLE `topics` ADD INDEX `topics_posted_idx` (`posted`)

ALTER TABLE `topics` ADD INDEX `topics_last_post_idx` (`last_post`)

Yeah, I know, two additional indexes, boo.. but it seems well worth it. Queries now only take a fraction of a second, instead of 20 seconds. Smaller forums may not encounter this problem now, but it's only a matter of time.