Fixed Query plan is not optimal while selecting from session_activity

[Pepelac]

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.*
    ,
    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:


and after forcing:

 

[digitalpoint]

Is the query with the forced index faster? There are times that the overhead of using an index can actually make the query slower.

 

[Yoskaldyr]

before FORCE INDEX
Run Time: 0.055693
with FORCE INDEX
Run Time: 0.027352

 

[Mike]

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.

 

[Mike]

The index format has been changed to be used correctly for 1.3.