Archive for Before posting please check the "stickies" in the support forums.
Please ask questions in real English and not "txt". You will get a better response.
Please do not ask support questions via PMs.
  Forum Index -> Forum Blogs
myff admin

Faster forums part three (slow queries)

Another review on slow queries on the forums shows up a very frequent culprit.

This is the bit that shows who is online. There are two notable issues there, one is that phpbb2 lacks an index on session_time and the other is that this is a query that is being made perhaps many times a minute whilst we don't really need to see the information changing more than once a minute at most.

The lack of an index whilst suspect is not necessarily wrong, as it is the sort of query where mysql may decide to ignore the index anyway. But we can and should cache the results and not repeat the query incessantly.
myff admin

I am also introducing an index for topic_last_post_id in the topics table.

There are a lot of queries that use it, and some evidence that some queries can go a little rogue and scan whole tables when this index is not present.

Some of these issues seem to occur with databases where the tables have not been optimized, which should not be an issue here since active forums to get an optimize once in a while.

I did already have the index available on the downsizer forum, and just sabotaged it by removing the index   There was no discernible effect.

However on balance it is responsible for a lot of unindexed queries, and a lot of people do seem to report benefits.
myff admin

It is a little hard to quantify, but since this change the database snapshots that log slow queries are not showing queries with last_post_id any more.

The snapshot system is not the same as the comprehensive slow query log system in mysql, but it is good enough to be showing the slowest points of database operation, so I think we can call this a success
myff admin

Hit on 3 more database routines this morning, two potentially nasty ones that I very glad to see sorted
myff admin

This is a small tweak that will occasional mean that posts since last visit will show a few more posts than it should.

The time it uses changes from:

$newpoststime = $userdata['user_lastvisit'];


$newpoststime = (intval($userdata['user_lastvisit']/3600)*3600);

This means the time is rounded to a granular hour. Which means in turn if you click on "View posts since last visit" on a forum with a lot of members, then there is far more likelyhood of the same query hitting the database and hence being cached for a quick result.
myff admin

I hate to do it, as it is tedious and the rewards are very small, but any query like:

      WHERE themes_id = ' . (int) $style;

is in efficient. It has to query the database to find the field names first.

the query should be:


'SELECT themes_id,template_name,style_name,head_stylesheet,body_background,body_bgcolor,body_text,body_link, body_vlink,body_alink,body_hlink,tr_color1,tr_color2,tr_color3,tr_class1,tr_class2,tr_class3, th_color1,th_color2,th_color3,th_class1,th_class2,th_class3,td_color1,td_color2,td_color3, td_class1,td_class2,td_class3,fontface1,fontface2,fontface3,fontsize1,fontsize2,fontsize3, fontcolor1,fontcolor2,fontcolor3,span_class1,span_class2,span_class3,img_size_poll,img_size_privmsg
      FROM ' . THEMES_TABLE . '
WHERE themes_id = ' . (int) $style;

The difference is probably about 5/10000 of a second, almost infinitesimal but still measurable time that can add up.

I once worked with someone who was adamant that nothing could be done to speed up his code, this for a company where the major problem was the code speed. First off a turned things on there head in terms of how the code was used. I can't tell but I'd guess that probably more than doubled the speed, then I set about all the little things, it took a long time but I think I squeezed 50% more out, slice by little slice. I'm certain that last squeeze made the difference between success and failure.
myff admin

I checked the query cache efficiency just before doing the latest posts change. It was 76.6% e.g. already quite an extraordinary proportion of eligible database queries don't have to be done again, the results can be picked directly from the cache.

A day later and we are running at 77.5% It would be rash to assert that the change is a certain cause of this increase, but all the same it is good.
myff admin

three days later and we have 80% Forum Index -> Forum Blogs
Page 1 of 1
Create your own free forum | Buy a domain to use with your forum