Yet Another Blog About Programming (YABAP)

Menu

Descending indexes in MariaDB

Since the dawn of time, MySQL indexes have a limit: they cannot be descending. Yes, ALTER TABLE and CREATE INDEX can contain the ASC and DESC keywords. It is perfectly legal in MySQL. But this does not mean that descending indexes are created. This is a well-known MySQL feature: when it cannot do something, it just pretends to. Well… someone thinks it is a feature. I think it is a bug (a bug is an unexpected behaviour), but what can we do.

The lack of support for descending indexes is only an issue when we need to create an index in which at least one column is ascending and at least one column is descending. For example, MySQL pretends to understand the following statement, but the resulting index won’t probably help us:

CREATE INDEX my_index ON my_table (my_column ASC, your_column DESC);

An ORDER BY my_column ASC, your_column DESC won’t be able to use this index.

MariaDB has a feature which provides a good workaround for the lack of descending indexes: the PERSISTENT columns. If you don’t know them, please check the MariaDB KnowledgeBase first.

You don’t know them but you didn’t follow the link, right? But you can’t escape my telepathic powers!

How can PERSISTENT columns help us creating mixed order index? If you didn’t already guess, I’ll tell you. Suppose we have a column A and we want it to be part of an ASC+DESC index. We’ll need to create a PERSISTENT column rev_A, whose values are calculated based on the values in A. The important thing here is that the order of rev_A is reversed. Here is an example:

The example table shows how to create a reversed index part for numbers, dates and ASCII 1-char strings. Multi-char ASCII strings are quite easy, too. I don’t know how to create a reversed Unicode column. If you find a way, please leave a comment below.