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!

Advertisements

Typing umlauts and Eszet on GNU/Linux

I started studying Deutsche (German). Since learning a language requires input and output in both their forms ({listening, reading}, {speaking, writing}), I needed to configure my Debian system to write the Deutsche-specific characters:

  • The umlaut on certain vowels: ÄÖÜ äöü
  • The Eszett (ess zed) or scharfes S (double S): ß
    • Capital ß does not exist in German, so I don’t use it. But if I want to type a non-existing letter, now I can: ẞ. For some reason, this only works with some programs, not with others.

To write these chars, you need to have a compose key. Debian (and all other distros? who knows) doesn’t have it by default, so you configure it in your desktop:

KDE

See this page.

GNOME

You have a GUI: Preferences/Keyboard.

Xfce

On Debian, edit /etc/default/keyboard.

Add/edit this line to use the left Microsoft key:
XKBOPTIONS="compose:lwin"

Other key names I know: rwin, menu, lctrl, rctrl, caps, paus, prsc, sclk.

Now you can type!

Restart the DE session, of course.

Typing umlauts:

Hit the compose key, THEN type double quotes, THEN type a lowercase or uppercase vowel. Then means that you are not supposed to press 999 keys altogether.

Typing Eszett:

Hit the compose key, THEN type s, THEN type s. If both the s are uppercase and your software supports stupidity, a non-existing letter will appear.

Stupidity matters

MariaDB [(none)]> SELECT 'ß'='ẞ' stupidity1, LOWER('ẞ')='ẞ' stupidity2;
+------------+------------+
| stupidity1 | stupidity2 |
+------------+------------+
|          0 |          1 |
+------------+------------+
1 row in set (0.00 sec)

UPDATE: I filed a bug for this. It’s now “Verified”, so they seem to agree this behavior is not optimal.