• Resolved sitmo2012

    (@sitmo2012)


    We have a rather large phpBB site, and our server crashed due to slow queries piling up. We traced it down to a query in the “updated topics” widget in this plugin.

    To solve it we changed the query in class.wp.w3all-phpbb.php

    from:

    SELECT * FROM ".$config["table_prefix"]."posts, ".$config["table_prefix"]."topics
    WHERE (SELECT MAX(topic_last_post_time) FROM ".$config["table_prefix"]."topics
    WHERE topic_visibility = 1)
    AND ".$config["table_prefix"]."topics.forum_id NOT IN(".$no_forums_list.")
    AND ".$config["table_prefix"]."posts.post_id = ".$config["table_prefix"]."topics.topic_last_post_id
    AND ".$config["table_prefix"]."posts.topic_id = ".$config["table_prefix"]."topics.topic_id
    AND ".$config["table_prefix"]."posts.post_visibility = 1
    ORDER BY post_time DESC LIMIT 0,$ntopics

    to:

    SELECT T.*, P.* FROM ".$config["table_prefix"]."topics AS T, ".$config["table_prefix"]."posts AS P
    WHERE T.topic_visibility = 1
    AND T.forum_id NOT IN(".$no_forums_list.")
    AND T.topic_last_post_id = P.post_id
    AND P.post_visibility = 1
    ORDER BY T.topic_last_post_time DESC LIMIT 0,$ntopics

    and were wondering if it would be a good idea to change this in the official plugin as well? There are a couple of different variant of the query being used in the plugin, I think they would all need this changed?

Viewing 4 replies - 1 through 4 (of 4 total)
  • Plugin Author axew3

    (@axewww)

    Any kind of collaboration, help and hint is really appreciated. I will be glad (as all others that use it i think) of course for any improvement and/or collaboration. All can be improved and, if needed changed (even the home site of the plugin).

    There are many points to be changed also about queries, scheduled from some time, and not still resolved.

    So yes of course, if (as i’m sure) your query lead to same result, is compatible with more MySQL versions, and maybe is also more fast!

    Thank you in the while. Will be tested and added on coming 1.5.0.

    Thread Starter sitmo2012

    (@sitmo2012)

    Thanks axew3 for the very quick reply and looking into this, this is much appreciated!

    Yes the query gives the same results indeed.

    If I can help you is some way then don’t hesitate to ask, I’ll he happy to help where I can.

    Plugin Author axew3

    (@axewww)

    Nice! Thank you Sitmo

    Plugin Author axew3

    (@axewww)

    P.s [solved for the moment]

Viewing 4 replies - 1 through 4 (of 4 total)

The topic ‘slow query’ is closed to new replies.