0003957: Support for descending indexes - Composr CMS feature tracker

ID	Project	Category	View Status	Date Submitted	Last Update

0003957	Composr	core	public	2019-11-18 20:27	2019-11-19 16:58

Reporter	Chris Graham	Assigned To
Severity	Feature-request
Status	non-assigned	Resolution	open
Product Version
Fixed in Version

Summary	0003957: Support for descending indexes
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).
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.
Tags	Type: Performance

Time estimation (hours)	5
Sponsorship open

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