order: metatags aren't indexed.

[evazion]

These order: metatags are prone to timing out for Members:

• order:change
• order:comment
• order:comment_bumped
• order:note
• order:mpixels
• order:portrait
• order:landscape

The problem is these things order by posts.field desc, posts.id desc, but the indexes are all single column. Either the indexes need to be multicolumn, or the queries need to drop the secondary posts.id desc order clause.

Timestamp based orderings could probably get away with dropping posts.id desc. The ordering would be unstable for two posts updated at the same time, but in practice that's probably rare.

For orderings based on width/height or filesize, collisions are probably more common so multicolumn indexes may be more necessary.

[r888888888]

For now I'll remove the secondary sort and see how it impacts searches in practice.

[Type-kun]

Uh, for score and favcount ordering must have secondary sorting, otherwise the order would be essentially random for images with same scores. This is worse than it sounds since it breaks pagination; we've already encountered this for comment ordering by score. Same goes for dimensional sorting, but it's less often used.

[evazion]

score and favcount don't have indexes either, so the secondary sort doesn't hurt them anyway. In fact, on second look, order:change, order:comment, order:portrait, and order:landscape aren't indexed at all either.

So last_comment_bumped_at and last_noted_at are the only things that really benefit from dropping the secondary sort. But it looks like these indexes still aren't being used, because they're defined by default as ASC NULLS LAST but the sort is doing DESC NULLS LAST.