• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.1 What is this query on index and why is it so slow?

Coop1979

Well-known member
#1
This query looks like it is scanning my entire thread table for some reason. Does anyone know what this query does (it looks like it has something to do with showing the latest thread reply in a forum)? And better yet, why does it take over a full second to run on my test site?

Code:
SELECT thread.*
    ,
    user.avatar_date, user.gravatar
FROM xf_thread AS thread
 
    LEFT JOIN xf_user AS user ON
        (user.user_id = thread.user_id)
WHERE ((thread.discussion_state IN ('visible'))) AND (thread.node_id IN (99, 86, 51, 28, 109, 110, 121, 122, 123, 124, 125, 115, 116, 127, 126, 128, 129, 130, 93, 105, 72, 94, 95, 96, 97, 98, 3, 22, 87, 68, 56, 21, 49, 91, 12, 54, 13, 14, 4, 26, 25, 60, 76, 102, 81, 61, 77, 59, 100, 80, 79, 58, 10, 27, 11, 52, 7, 55, 17, 18, 20, 5, 106, 117, 112, 111, 108, 107, 101, 24, 15, 19, 16, 8, 103, 29))
ORDER BY thread.post_date DESC
LIMIT 5
Run Time: 1.108686
Select Type    Table    Type    Possible Keys    Key    Key Len    Ref    Rows    Extra
SIMPLE    thread    ALL    node_id_last_post_date,node_id_sticky_last_post_date                  102837    Using where; Using filesort
SIMPLE    user    eq_ref    PRIMARY    PRIMARY    4    pcxf_db.thread.user_id    1
No other queries even take 100th of a second to run.