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.
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!
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
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. :)
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.
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 (http://www.simplemachines.org/community/index.php?topic=289878.0). It makes a big difference: my entire stats (http://www.curvage.com/forum/index.php?action=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. :)
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
http://www.curvage.com
Interesting niche.
How is that site relevant?
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).
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.
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?
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?
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.
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. :)
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 (http://www.simplemachines.org/community/index.php?topic=289878.0). It makes a big difference: my entire stats (http://www.curvage.com/forum/index.php?action=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)
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.
Great tip, Phalloidium :)
Quote from: Sarge on May 08, 2009, 02:31:50 AM
Great tip, Phalloidium :)
I'm a performance junkie ;)
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
woow, this is really good, now stats dispay in second... :)
Thans very much for this tip.,.,
Btw, i hope this wont slow down displaing forums page...?
Quote from: Dzonny on May 31, 2009, 08:39:58 AM
woow, this is really good, now stats dispay in second... :)
Thans very much for this tip.,.,
Btw, i hope this wont slow down displaing forums page...?
It won't. There is some minor overhead in maintaining extra indexes, but it's not noticeable.
Sorry to bump this really, really old thread, but is this fix still valid for SMF 1.1.13 and is something that can/should be done, or is this an issue that has been resolved in code and should no longer be considered for 1.1.13 forum owners?
Thanks in advance.
As far as I know this was not implemented in the 1.1 updates so it would still be relevant.
Thank you, Arantor.