forum_stickies query is not very useful on large sites
|Reported by:||mdawaffe||Owned by:|
We were having some SQL issues on WordPress.org's support forums and found that
KEY forum_stickies (topic_status,forum_id,topic_sticky,topic_time)
wasn't all that useful.
For queries like
SELECT t.* FROM bb_topics AS t WHERE t.forum_id = '1' AND t.topic_status = '0' AND t.topic_sticky = '0' ORDER BY t.topic_time DESC LIMIT 30
the index was not used. Instead KEY forum_time (forum_id,topic_time) was used. Possibly because on large sites, that index is so large that working with a smaller index actually give a performance boost. I'm not sure.
And, since forum_id is in the middle there, the index was not used on queries like
SELECT t.* FROM bb_topics AS t WHERE t.topic_status = '0' AND t.topic_sticky = '0' ORDER BY t.topic_time DESC LIMIT 30;
We ended up removing that index and instead putting in
KEY stickies (topic_status,topic_sticky,topic_time)
Which greatly improved the efficiency of that second query (and didn't effect the first one since it still used the forum_time index).