View Issue Details

IDProjectCategoryView StatusLast Update
0003957Composrcorepublic2019-11-19 16:58
ReporterChris GrahamAssigned To 
Severityfeature 
Status non-assignedResolutionopen 
Product Version 
Fixed in Version 
Summary0003957: Support for descending indexes
DescriptionMySQL 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).
Additional InformationAn 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.
TagsType: Performance
Attach Tags
Time estimation (hours)5
Sponsorship open

Relationships

related to 0003960 non-assigned Re-review all indexes 

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2019-11-18 20:27 Chris Graham New Issue
2019-11-18 20:27 Chris Graham Tag Attached: Type: Performance
2019-11-19 16:58 Chris Graham Relationship added related to 0003960