Howto: Make your stats page display way faster in SMF 1.1

Started by Something like that, February 02, 2009, 06:03:35 AM

Previous topic - Next topic

Something like that

This will make your statistic page load way faster. By default, SMF does not have several database indexes that drastically speed the statistics calculations up.

If you know what a database index is, and how to add one, these are the ones you need to add:
  • in the smf_members table, indexes on gender and totalTimeLoggedIn
  • in the smf_topics table, indexes on numReplies and ID_MEMBER_STARTED

    Enjoy.

thomasdev

Quote from: Phalloidium on February 02, 2009, 06:03:35 AM
This will make your statistic page load way faster. By default, SMF does not have several database indexes that drastically speed the statistics calculations up.

If you know what a database index is, and how to add one, these are the ones you need to add:
  • in the smf_members table, indexes on gender and totalTimeLoggedIn
  • in the smf_topics table, indexes on numReplies and ID_MEMBER_STARTED

    Enjoy.
wow, that was a great tip. much faster now :)
thank you very much!

Afro

Quote from: Phalloidium on February 02, 2009, 06:03:35 AM
This will make your statistic page load way faster. By default, SMF does not have several database indexes that drastically speed the statistics calculations up.

If you know what a database index is, and how to add one, these are the ones you need to add:
  • in the smf_members table, indexes on gender and totalTimeLoggedIn
  • in the smf_topics table, indexes on numReplies and ID_MEMBER_STARTED

    Enjoy.
can you elaborate more on what you meant here cos i am lost. I know where to find database the database smf_member table.
.but i dont know what to add there..

thank you

Something like that

There are many methods of adding an index. You could do it on the MySQL command line, or with tools like phpMyAdmin.

I'd suggesting googling on how to add an index to MySQL. I'm not going to say here as I don't want to encourage people playing around with stuff they don't know about and be responsible for any disasters they create. :)

karlbenson

I added a link to this topic onto an existing bug report that already suggested adding an index on the total time logged in.
http://dev.simplemachines.org/mantis/view.php?id=691

Hopefully the devs will consider the possibility of adding an index for other items which you mentioned.

Something like that

Can you please also add that members.realName should be converted to a varchar? It's a tinytext by default, and having any text or blob columns will always cause a filesort with a temporary table. There is more information on that here. It makes a big difference: my entire stats page loads in 0.060 seconds on a 30,000 member forum. So much better than the several whole seconds it used to take. :)

Antechinus

Hey this is awseome. I have no idea what I'm doing when it comes to adding indices but I can wreck things on my local before I go live. ;D



Something like that

Quote from: Antechinus on February 04, 2009, 07:53:26 PM
Hey this is awseome. I have no idea what I'm doing when it comes to adding indices but I can wreck things on my local before I go live. ;D

It's really not too complicated. It's one of the first things to learn about databases.

You might not get the ~0.060 page generations I do though, because I've optimised a lot of other things. You should easily see under 0.300 though versus the seconds it would take before (on a moderately loaded machine).

Something like that

Quote from: ApplianceJunk on February 04, 2009, 07:54:03 PM
http://www.curvage.com

Interesting niche.

Different strokes for different folks. :)

Quote from: Antechinus on February 04, 2009, 07:58:02 PM
How is that site relevant?

I linked to the stats page on curvage.com above, so you can see how fast stats can happen.

Antechinus

I don't imagine it's too difficult. I just haven't had to do it before.

Two questions though:

1/ how does adding the extra indices to those tables affect scripts like upgrade.php?

2/ does this also work with SMF 2? 

ApplianceJunk

Quote from: Antechinus on February 04, 2009, 07:58:02 PM
How is that site relevant?

It's Phalloidium, the topic starters website.
Did you not look at his stats page he posted a link too?


Something like that

Quote from: Antechinus on February 04, 2009, 08:05:06 PM
I don't imagine it's too difficult. I just haven't had to do it before.

Two questions though:

1/ how does adding the extra indices to those tables affect scripts like upgrade.php?

2/ does this also work with SMF 2? 

1. I can't say for certain, as those scripts could do anything, but it probably won't affect anything. Again, this is one reason why I'm not telling people how to do this -- they should have the knowledge to fix things on their own if the upgrade script causes trouble.

2. Mostly likely. I haven't checked to see if the table and column names are the same, but something similar can be done. Look at the ORDER BY part of the queries and make sure there is an index on each columns it orders by.

Antechinus

Quote from: Phalloidium on February 04, 2009, 08:13:41 PM1. I can't say for certain, as those scripts could do anything, but it probably won't affect anything. Again, this is one reason why I'm not telling people how to do this -- they should have the knowledge to fix things on their own if the upgrade script causes trouble.

2. Mostly likely. I haven't checked to see if the table and column names are the same, but something similar can be done. Look at the ORDER BY part of the queries and make sure there is an index on each columns it orders by.

Ok. Well if it came to it one obvious ploy would be to remove the extra indices before running upgrade.php or install.php and reinsert them afterwards. That should be simple enough and bulletproof.

@AJ: No. I missed the link the first time around. :)

karlbenson

Quote from: Phalloidium on February 04, 2009, 07:45:48 PM
Can you please also add that members.realName should be converted to a varchar? It's a tinytext by default, and having any text or blob columns will always cause a filesort with a temporary table. There is more information on that here. It makes a big difference: my entire stats page loads in 0.060 seconds on a 30,000 member forum. So much better than the several whole seconds it used to take. :)

One of the team has already added a separate bug report for that
http://dev.simplemachines.org/mantis/view.php?id=3012

(Although it might be slightly related, we use one bug report for each thing, and it helps us to keep track of where we're upto)

R.Bourne

Wow! Thanks for the tip. Honestly, I had no idea what or how to do this. Google'd and learned how to do it with phpMyAdmin and it took me less than a min. I can see the difference now and my forum is still up and running! ;) Thank you.

Sarge


    Please do not PM me with support requests unless I invite you to.

http://www.zeriyt.com/   ~   http://www.galeriashqiptare.net/


Quote
<H> I had zero posts when I started posting


Aleksi "Lex" Kilpinen

Quote from: Phalloidium on May 08, 2009, 08:40:43 PM
Quote from: Sarge on May 08, 2009, 02:31:50 AM
Great tip, Phalloidium :)

I'm a performance junkie ;)
I take it you don't need support on this? ;)
Want to mark this solved? :) Or should I? ;D
Slava
Ukraini!


"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum. Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

How you can help SMF

Advertisement: