XF 1.4 Slow queries on very large forums

[nrep]

I'm testing out upgrading a forum with several million threads, which contains many forum categories with hundreds of thousands of posts.

I did a test XF import and it works really well (and fast) on smaller forum sections, however if I start browsing a deep forum section page (i.e. page 10,000 of a forum), then things crawl to a halt. The further back I go, the worse it gets. I can use up to page 2,000 without much of a delay, but if I start to access page 10,000+ I get timeouts (over 30 seconds).

Here's an example of one of the slow queries I get from debug mode:

SELECT thread.*
    ,
    user.*, IF(user.username IS NULL, thread.username, user.username) AS username,
    deletion_log.delete_date, deletion_log.delete_reason,
    deletion_log.delete_user_id, deletion_log.delete_username,
        IF(thread_read.thread_read_date > 1420400047, thread_read.thread_read_date, 1420400047) AS thread_read_date,
    IF(thread_watch.user_id IS NULL, 0,
        IF(thread_watch.email_subscribe, 'watch_email', 'watch_no_email')) AS thread_is_watched,
    thread_user_post.post_count AS user_post_count
FROM xf_thread AS thread

    LEFT JOIN xf_user AS user ON
        (user.user_id = thread.user_id)
    LEFT JOIN xf_deletion_log AS deletion_log ON
        (deletion_log.content_type = 'thread' AND deletion_log.content_id = thread.thread_id)
    LEFT JOIN xf_thread_read AS thread_read ON
        (thread_read.thread_id = thread.thread_id
        AND thread_read.user_id = 1)
    LEFT JOIN xf_thread_watch AS thread_watch
        ON (thread_watch.thread_id = thread.thread_id
        AND thread_watch.user_id = 1)
    LEFT JOIN xf_thread_user_post AS thread_user_post
        ON (thread_user_post.thread_id = thread.thread_id
        AND thread_user_post.user_id = 1)
WHERE (thread.node_id = 152) AND (thread.sticky = 0) AND (thread.discussion_state IN ('visible','deleted','moderated'))
ORDER BY thread.last_post_date DESC
LIMIT 20 OFFSET 162380
Run Time: 12.230421

Select Type    Table    Type    Possible Keys    Key    Key Len    Ref    Rows    Extra      
SIMPLE    thread    ref    node_id_last_post_date,node_id_sticky_state_last_post    node_id_last_post_date    4    const    552480    Using where      
SIMPLE    user    eq_ref    PRIMARY    PRIMARY    4    testsite.thread.user_id    1          
SIMPLE    deletion_log    eq_ref    PRIMARY    PRIMARY    31    const,testsite.thread.thread_id    1    Using where      
SIMPLE    thread_read    eq_ref    user_id_thread_id,thread_id    user_id_thread_id    8    const,testsite.thread.thread_id    1          
SIMPLE    thread_watch    eq_ref    PRIMARY,thread_id_email_subscribe    PRIMARY    8    const,testsite.thread.thread_id    1          
SIMPLE    thread_user_post    eq_ref    PRIMARY,user_id    PRIMARY    8    testsite.thread.thread_id,const    1

This forum runs really well on vBulletin, so I'm very puzzled as to why it crawls on XF? I would consider MySQL to be well optimised - other sites on the server fly, as does the VB version of the site.

 

[eva2000]

I hope he bundles them all up as one big performance addon.

+1

 

[Xon]

Was this ever resolved in XF or through an addon?

I haven't found anything that was able to fix it unfortunately, although this addon did help in other areas:

https://xenforo.com/community/resources/optimized-list-queries-by-xon.4487/

The optimize list queries add-on does implement the 'better' query style for forums with large page counts. Under the performance tab in Options you can tune when it kicks in vs uses the standard XenForo forum list query.

 

[Xon]

Any chance this could be resolved with XF2's Finder system?

There looks to be enough information available to the Finder to generate a sub-select, followed by re-joining table to pull the selected columns for output.

 

[Mike]

It's something I've certainly thought about.