• 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

Pepelac

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

This query is not using index on the view_date column
Code:
SELECT session_activity.*
    ,
    user.*,
    user_profile.*,
    user_option.*
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:
1.png

and after forcing:

2.png
 

Mike

XenForo developer
Staff member
#4
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.