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

Fixed Query plan is not optimal while selecting from session_activity


Well-known member
I clearly understand that this is not a XF issue, but...

This query is not using index on the view_date column
SELECT session_activity.*
FROM xf_session_activity AS session_activity

    LEFT JOIN xf_user AS user ON
        (user.user_id = session_activity.user_id)
    LEFT JOIN xf_user_profile AS user_profile ON
        (user_profile.user_id = user.user_id)
    LEFT JOIN xf_user_option AS user_option ON
        (user_option.user_id = user.user_id)
WHERE (session_activity.view_date > 1382086508)
ORDER BY session_activity.view_date DESC
It could be "fixed" by forcing an index. For example, query plan before forcing the index on view_date column:

and after forcing:



XenForo developer
Staff member
Forcing the index shouldn't work normally. That index is likely a hash index rather than a btree so range selections don't work. If you're affected (though this table will usually be small anyway; if it grows too big, it may be indicative of cleanup crons not running), you can just change the index to btree.

I'm not going to change this in a bug fix release though, so it would be resolved in something like 1.3.