MariaDB is not Using Hash Unique Key on InnoDB

Question

I create a table with a text column with unique key:

create table a(id int auto_increment key,a text unique);

And when I run show create table a;, it says that the unique key is using hash:

CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

What I am expecting is that when I insert into a (a)values('a'); and then select * from a where a='a';, it should use that key for searching. However it does not seem to, the query is slow on large dataset, and explain select * from a where a='a'; produces the following:

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | a     | ALL  | a             | NULL | NULL    | NULL | 1    | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

Why MariaDB is not using that key for searching?

When I add another key manually alter table a add key b(a) using hash;, it works as expected, just a warning Specified key was too long; max key length is 3072 bytes.

Answer 1

Per MDEV-13445, the MariaDB optimizer doesn't yet support using HASH indexes on text types.

Also see MDEV-31072.