Re: Get latest topics performance “problem” and “solution”
I bet it only doubles the performance when it can sort in memory. Once it spills into a temporary disk sort, then you start seeing a more radical increase in performance between the forced index and not.
I should also point out the mini-plugin (and index) will also help on any forum/sub-forum page in addition to the front-page. So all that extra index storage is at least multi-purpose.
One way to boost this even further would be to ORDER BY the topic_last_post_id and then use the last_post_id as a UNIQUE index (key) since no two posts can have the same post_id (and no two topics can have the same last post_id). topic_time is not unique at all so it takes a tad longer for mysql to sort/larger index.