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:

CREATE TABLE `exam_scores`
(
	  `id` INT SIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
	, `student_name` VARCHAR(200) NOT NULL
	, `exam_code` CHAR(1)
	, `exam_score` SMALLINT UNSIGNED NOT NULL
	, `exam_date` DATETIME NOT NULL
	
	-- auxiliary columns
	, `rev_exam_code` TINYINT UNSIGNED AS (255 - ORD(exam_code)) PERSISTENT
	, `rev_exam_score` SMALLINT SIGNED AS (0 - exam_score) PERSISTENT
	, `rev_exam_date` SMALLINT UNSIGNED AS (DATEDIFF('2050-01-01', exam_date)) PERSISTENT
	
	-- mixed indexes
	, INDEX `desc_exam_code` (`rev_exam_code`, `student_name`, `exam_code`)
	, INDEX `desc_exam_score` (`rev_exam_score`, `student_name`)
	, INDEX `desc_exam_date` (`rev_exam_date`, `student_name`, `exam_date`)
)
	ENGINE = InnoDB
	DEFAULT CHARACTER SET = 'utf8'
;
-- this is needed because some rev* columns return negative values
SET @@global.sql_mode := CONCAT(@@global.sql_mode, ',NO_UNSIGNED_SUBTRACTION');
SET @@session.sql_mode := @@global.sql_mode;
INSERT INTO `exam_scores`
		(`student_name`, `exam_code`, `exam_score`, `exam_date`)
	VALUES
		  ('Anthony Stark', 'F', 100, '1994-01-01')
		, ('Klark Kent', 'G', 60, '2000-01-01')
		, ('Corto Maltese', 'A', 50, '2007-01-01')
		, ('Tin Tin', 'Z', 900, '2005-01-01')
		, ('Philip Mortimer', 'C', 20, '2000-01-01')
		, ('Dago', 'M', 80, '2004-01-01')
	;

Yes, this table sucks. It is not relational, etc, etc. But we are talking about mixed order indexing in MariaDB, not relational theory.

Is it clear now? I hope so. Let’s see an example query. We want to list the students: recent exams first, and if two dates are identical, sort them alphabetically.

MariaDB [test]> EXPLAIN SELECT `student_name`, `exam_date`
    -> FROM `exam_scores`
    -> ORDER BY `rev_exam_date` ASC, `student_name` ASC;
+------+-------------+-------------+-------+---------------+----------------+---------+------+------+-------------+
| id   | select_type | table       | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+------+-------------+-------------+-------+---------------+----------------+---------+------+------+-------------+
|    1 | SIMPLE      | exam_scores | index | NULL          | desc_exam_date | 613     | NULL |    7 | Using index |
+------+-------------+-------------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

The desc_exam_score index shows that including the original column in the index can be unnecessary:

MariaDB [test]> EXPLAIN SELECT `student_name`, -`rev_exam_score` FROM `exam_scores` ORDER BY `rev_exam_score` ASC, `student_name` ASC;
+------+-------------+-------------+-------+---------------+-----------------+---------+------+------+-------------+
| id   | select_type | table       | type  | possible_keys | key             | key_len | ref  | rows | Extra       |
+------+-------------+-------------+-------+---------------+-----------------+---------+------+------+-------------+
|    1 | SIMPLE      | exam_scores | index | NULL          | desc_exam_score | 605     | NULL |    6 | Using index |
+------+-------------+-------------+-------+---------------+-----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

-column is obviously a quick operation.

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.

Enjoy!

About these ads

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s