View of all forums extremely slow when plugin is enabled

  • Resolved quan_flo

    (@quan_flo)


    8 years, 5 months ago

    Hi,

    first thank you for the great plugin!

    It seems to work fine but is it also testet with huge forum databases?

    My forums have more than 100.000 posts. After activating the plugin the page of bbpress that shows all forums is extremely slow. It takes ten seconds to load.
    When I modify your function has_user_read_all_forum_topics( $forum_id,$user_id=false ) with a “return true;” at the beginning to skip your code the forum page is loaded in less than two seconds. So there seems to be a bottleneck. I updated from 1.0.6 to recent version and the older version seemed to be a little bit more performant for me..

    It would be great if your plugin also could handle forums with many posts.
    Otherwise a funktion or a filter to disable your plugin just for this page would be fine.

    I also do not understand line 504 and 505:

    $transient_duration = $this->get_options('count_topics_transient_duration');
$transient_duration = null;

    What is the sense of getting the duration first and then overwriting the value with null?

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

  • Hi and thanks for your feeback.
    The line
    $transient_duration = null;
    should not be there, I made a fix (1.2.8).

    Here is how works the function *has_user_read_all_forum_topics* :
    1/ query to get all the topics of a forum
    2/ query to get all the read topics of that forum
    3/ if the counts of posts for those two queries matches, then the forum is considered as read.

    Indeed, that could return a LOT of posts, and that is probably why it is slowing down your forums.

    I have to find another solution here.

    My first idea would be to use an ‘offset’ :

    Do compare the counts of the two queries on a base of, let’s say, 100 items; and abord once it does not match : so if a recent post is unread, it will stops at the first cycle and will not need to query all the posts.

    Mhh… Let me think about it. Ideas are welcome !

    Now (v1.2.9) the two queries are only fetching post IDs instead of full posts, could you give me your feedback ?

    Thanks !

    I did try to update the code to make the queries faster; please update to 1.3.0.
    It may make a difference.

    For feedback purpose, you can enable the debug log to get various informations about the plugin, including the queries times.

    I’m trying to find out something to speed up the queries even more.

    Could you try to run this query within phpMyAdmin, and tell me an average of the time the query takes and how much posts are returned?

    SELECT wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key = '_bbp_last_active_time' AND wp_postmeta.meta_value > '2015-10-04 11:51:05' ) AND ( mt1.meta_key = 'bbppu_read_by' AND mt1.meta_value = '1' ) ) AND wp_posts.post_type = 'topic' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed' OR wp_posts.post_status = 'private')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

    Mine is about ~0.03 seconds, returning 233 posts.

    This is about 25% faster than the queries from v1.2.7, which was :

    SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key = '_bbp_last_active_time' AND wp_postmeta.meta_value > '2015-10-04 11:51:05' ) AND ( mt1.meta_key = 'bbppu_read_by' AND mt1.meta_value = '1' ) ) AND wp_posts.post_type = 'topic' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed' OR wp_posts.post_status = 'private')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

    I also updated the cache system so this query is not run everytime < v 1.2.8.

    It would help me to know how much time it takes on forums like yours, with a huge amount of posts. Thanks !

    Thread Starter quan_flo

    (@quan_flo)

    First thanks for your efforts!

    I tried the update right now, instead of ten seconds it from 3-5 seconds to load. Thats really better and ok! Thanks!

    Debug log tells me thgat query time is no longer a problem.

    there is a type in the code for the debug log. “user … has real all” whoud be “user … has read all” but thats not important…

    But in general: Why dont you save last visit timestamp of a user in each forum with latest activity of the forum? I think this might save much time.

    Thread Starter quan_flo

    (@quan_flo)

    I forgot to execute the query.
    first query: first execution: 0.2075 seconds, next executions (sql cache) 0.0004 seconds. returns 11 results. wp_posts table has 391.064 rows.
    second query: 0.24 seconds, next executions: 0.0005 seconds.
    The queries seem to be quite similar in execution time at my system

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘View of all forums extremely slow when plugin is enabled’ is closed to new replies.