Re: Get latest topics performance “problem” and “solution”
That’s funny I was just about to make a TRAC ticket pointing out there is no index on topic_time for the latest view.
Strange you have to force the index though, what is the default ORDER BY, let me go look…
yup, they order by topic_time, with no index, that’s just crazy, even for just 1000 topics, because it would have to do a full column scan. Ordering by topic_last_post_id would be even faster with an index too though I guess in a very strange circumstance the last post might not be the last time.
Still, you should not have to force the index.
Oh wait, I see why – it’s because mysql decides to use the stickies key instead, ugh.
Yeah I can see why that would bring a large forum to a crawl.
I think whomever designed the bbPress database tables had a misunderstanding how mysql indexes work because they are using multiple field indexes that are not the primary key. Or a key at all. Which is useless because mysql will only use one index per query regardless of it’s complexity (or lack of). An index on multiple fields is useless unless it’s used as a key. MySQL 5 can use different indexes for the query vs ORDER BY or GROUP BY but bbPress doesn’t take advantage of that at all.
