Speeding up the phpBB 2 forum index page

---

I just found a very effective way to speed up the forum index page of a phpBB 2 forum.

The problem is the following query:

Code:

$sql = "SELECT t.forum_id, t.topic_id, p.post_time          FROM " . TOPICS_TABLE . " t, " . POSTS_TABLE . " p          WHERE p.post_id = t.topic_last_post_id             AND p.post_time > " . $userdata['user_lastvisit'] . "             AND t.topic_moved_id = 0";

As you can see, it's a join between the posts table and the topics table. The problem is that the topic_last_post_id column has no index! This causes mysql to go through the whole topics table. You can imagine that for forums with a lot of topics (like our 50,000+) this is a nightmare.

The solution is to add a single index on the topic_last_post_id table. This reduces the query from a nightmare, to an average one!

To fully explain the effect, here are the times it takes to load the index page:
Without index on the old server: 2-30s (for me, could be worse for others not logging in very often)
Without index on the new server: 0.8-1.5s
Now with index on the new server: 0.04-0.07s

That's an improvement of 10-30 times on average! And I have absolutely no idea why phpBB 2 doesn't add this index on the default installation. I really hope they added it in phpBB 3.



7 blog comments below

I'm so going to try this out on my forum

soulery on Mon Sep 03, 2007 11:12 pm

I am not sure if they added this to phpBB 3 cuz I never looked at the source code, but I have a phpBB3 forum and it goes WAY FASTER than my old phpBB2 forum did. Then again.... my forum never was really slow in the first place, but oh well.

-Nick

polly-gone on Thu Sep 27, 2007 10:59 pm

So php 3 is actually faster?

bow_death4 on Tue Jul 22, 2008 6:34 am

i will try it as soon as possible...
thanks

birebirozelders on Mon Oct 06, 2008 1:03 pm

I like this code, but I use php 3 :/

ArmeniansUnited on Thu Apr 23, 2009 5:59 pm

Hmm, this seems interesting thanks.

scarpellid on Thu May 21, 2009 2:01 am

Wowww!!! .. I will try that ^__^

leondevil on Tue Sep 08, 2009 8:25 am