[bobbyh]

Sam, yes, you’re totally right, sorry I wasn’t thinking. mysql can only use one index per query, and a multi-column index is much more restrictive for this common query, and hence much faster. This will really speed things up, so I’ll add these indices tonight (when server load goes down). Thanks!

I also think it’s worthwhile to add this multi-column index to the core. What do you think?

EDIT: I was too curious, so I added a 3-column index just now. When I did an EXPLAIN statement on my mysql 4.1 database, this 3-column index was listed as a possible_key, but not used as a key. The query with a 3-column index thus didn’t use an index and required a “where” and a “filesort” (for topic_time). I then added topic_time to the index, making this a 4-column index, and mysql started using the 4-column index as the key, which also eliminated the slow “filesort”. This is the probably the most common query bbpress does, so making it faster is very exciting!