Unfortunately I'm using 8.0.4 and this is for a government website, I only get so many maintenance windows. Is this the only workaround for this issue?
I did make a test index as you described on my test box and tried the query and it used the new index. However, ORDER BY forum_id then last_post_time is simply not the intended sorting order. (Though I'm considering just SELECTing the topic_last_post_time field and resorting the results in the script if this is the only workaround). - Adam
Adam Alkins <adam.alkins@gmail.com> writes: > SELECT t.topic_id > FROM phpbb_topics AS t > WHERE t.forum_id = 71 > AND t.topic_id NOT IN (205026, 29046, 144569, 59780, 187424, > 138635, 184973, 170551, 22419, 181690, 197254, 205130) > ORDER BY t.topic_last_post_time DESC > LIMIT 23 OFFSET 0
If you're using 8.1, you'd probably find that an index on (forum_id, topic_last_post_time) would work nicely for this. You could use it in prior versions too, but you'd have to spell the ORDER BY rather strangely: ORDER BY forum_id desc, topic_last_post_time desc The reason for this trickery is to get the planner to realize that the index order matches the ORDER BY ...
