You are invited to Log in or Register a free Frihost Account!

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:
$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.

23 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 Smile Smile Smile
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...
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
I am not sure but i will try it as soon as possible...
aldenis on Thu Jun 03, 2010 2:35 am
Throughout the months of running phpBB I've made a few mods here and there to help speed things up on the site due to the high volume of traffic. Just wanted to take a short break and share with everyone some of the modifications that I made. They're by no way an ehnancement to phpBB in any ways, but those who are experiencing slowness in their large forums might found interesting:

- Index page

By default phpBB collects all topics that are new to see which forum have been updated since your last visit. When user post count approached 50,000 posts a day this process took more than 10 seconds to load. Eventually the process was dropped, and a cheat was put in-place.
migs_hernan on Thu Aug 26, 2010 4:11 am
did it work?
marold on Tue May 24, 2011 6:22 pm
Maybe I will try this sometime
ljyxfztf1984 on Fri Jul 01, 2011 11:01 am
derienp on Sat Jul 09, 2011 12:40 am
Very good job, guys.
tiedostokellari on Sat Dec 10, 2011 3:48 pm
i liked the code but ... i think php cool and afford my needs
kolastreet on Mon Jan 02, 2012 8:58 pm
I think that I will go for it. Keep doing a good job guys !
tomiwoj on Sun Mar 11, 2012 10:42 pm
very fine.
tomiwoj on Sun Mar 11, 2012 10:42 pm
nanan02 on Thu Sep 13, 2012 4:01 am
Thats awesome!
dinner2go on Sat Sep 15, 2012 9:54 pm
I haven't really used the system before but I have heard about its limitations.
dinner2go on Sat Sep 15, 2012 9:55 pm
maybe i can use it too
marqx on Sun Nov 18, 2012 3:23 am
I can use it too
monalisha on Tue Feb 12, 2013 5:46 pm
Anyone who show me how does MySQL index columns when we use INNER JOIN
loivu on Sun Mar 10, 2013 5:08 pm
that just went way over my head
borkensyntax on Sun Oct 27, 2013 3:45 am

© 2005-2011 Frihost, forums powered by phpBB.