#3957 - Support for descending indexes

Identifier #3957
Issue type Feature request or suggestion
Title Support for descending indexes
Status Open
Tags

Type: Performance (custom)

Handling member Deleted
Addon core
Description MySQL 8 adds support for descending indexes.

This is particularly relevant for indexes where we sort by descending timestamps (e.g. viewing in a forum).

It improves index performance by about 15%, avoiding the 'Backward index scan'. I'm not sure exactly why a backward index scan is slower, because a B+Tree index should be traversable easily in any order. Possibly because when there are multiple rows in the same index bucket it has to back-traverse a singly linked list for those rows in the bucket. That's speculation, I don't know.

I don't consider this issue particularly important. It's a relatively slow performance gain, and we have to add complexity in the index meta databases, and re-do a load of indexes, all for what is still a minority of users (few people are on MySQL 8 and few of those will notice any performance difference). I wonder if we'd end up wanting to duplicate a load of indexes rather than re-doing them - so that we can efficiently retrieve results in both direction. Mirroring indexes has a performance implication (it slows writes).
Steps to reproduce

Additional information An alternative workaround would be to store inverted values, e.g. the negative value of a timestamp, and then sort by that. That's a horrible kludge that I don't want infecting our DB design and code though, especially when we now have a proper way to handle it.
Funded? No
The system will post a comment when this issue is modified (e.g., status changes). To be notified of this, click "Enable comment notifications".

Rating

Unrated