MariaDB implements function Indexing

We know that MySQL currently does not support function indexing. Currently, most databases include PostgreSQL and Oracle. What is a function index?
Function indexes use a fixed function to generate an index tree based on the function result set. The advantage is that it is easy and easy for developers to write SQL statements, but the same is true for the poor. They must write read and filter statements based on fixed conditions.


Before that, if you want to implement this function, MySQL needs to create a new column and then use a pre-trigger to modify the value of this column. Now, MariaDB has a virtual column feature that can be easily implemented.
Let's take a look at the table structure in PostgreSQL.

t_girl=# \d email_list;             Table "public.email_list"  Column  |            Type             | Modifiers ----------+-----------------------------+----------- id       | integer                     |  email    | character varying(200)      |  log_time | timestamp without time zone | Indexes:    "idx_email_suffix" btree (substr(email::text, "position"(email::text, '@'::text) + 1))

The EMAIL column attribute of this table has a function index to find which provider the secondary EMAIL attribute belongs to, such as 163 and GMAIL.


We generated 20 million rows of records for a table.

t_girl=# select count(*) from email_list; count  -------- 200000(1 row)Time: 39.851 ms

Now you can perform the corresponding query. If you do not strictly follow the creation specifications of this function, the query will not go through the index. Therefore, you must strictly write SQL statements.

                                                              QUERY PLAN                                                              -------------------------------------------------------------------------------------------------------------------------------------- Aggregate  (cost=1607.19..1607.20 rows=1 width=12) (actual time=5.514..5.514 rows=1 loops=1)   ->  Bitmap Heap Scan on email_list  (cost=48.29..1602.08 rows=2047 width=12) (actual time=1.126..4.806 rows=1960 loops=1)         Recheck Cond: (substr((email)::text, ("position"((email)::text, '@'::text) + 1)) = '56.com'::text)         ->  Bitmap Index Scan on idx_email_suffix  (cost=0.00..47.78 rows=2047 width=0) (actual time=0.802..0.802 rows=1960 loops=1)               Index Cond: (substr((email)::text, ("position"((email)::text, '@'::text) + 1)) = '56.com'::text) Total runtime: 5.603 ms(6 rows)Time: 6.601 ms

From the query analysis plan, we can see that this function index scans around 2 k rows of records and generates 1960 rows of result sets.

t_girl=# select count(email) as num from email_list where substr(email,position('@' in email)+1)='56.com'; num  ------ 1960(1 row)Time: 5.251 mst_girl=# 

Next, let's look at how to implement the corresponding functions in MariaDB.
The table structure is as follows:

MariaDB [t_girl]> show create table email_list;+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table      | Create Table                                                                                                                                                                                                                                                                                                |+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| email_list | CREATE TABLE `email_list` (  `id` int(11) DEFAULT NULL,  `email` varchar(200) DEFAULT NULL,  `log_time` datetime(6) DEFAULT NULL,  `email_suffix` varchar(100) AS (substr(email,position('@' in email)+1)) PERSISTENT,  KEY `idx_email_suffix` (`email_suffix`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)

Here we use the virtual column of MariaDB and specify the persistent attribute for the virtual column, so that it can be viewed as a real attribute.
Line. We will use this virtual column for query. However, this is simple, and the query statement does not need to be so strict, just like a common statement.

MariaDB [t_girl]> explain select count(email) from email_list where email_suffix = '56.com';+------+-------------+------------+------+------------------+------------------+---------+-------+------+-----------------------+| id   | select_type | table      | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |+------+-------------+------------+------+------------------+------------------+---------+-------+------+-----------------------+|    1 | SIMPLE      | email_list | ref  | idx_email_suffix | idx_email_suffix | 103     | const | 1959 | Using index condition |+------+-------------+------------+------+------------------+------------------+---------+-------+------+-----------------------+1 row in set (0.02 sec)

Of course, the query speed is very fast.

MariaDB [t_girl]> select count(email) from email_list where email_suffix = '56.com';         +--------------+| count(email) |+--------------+|         1960 |+--------------+1 row in set (0.02 sec)