View Issue Details
|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|
|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.|
|Time estimation (hours)||5|