MySQL5.7 implement expression indexing for virtual columns _ MySQL

MySQL has not provided such complex functions as function indexing since ancient times. So how can this function be implemented in MySQL? Let's take a look at the concept of function indexing. Function indexes, also known as Expression indexes, are indexes created based on fields using specific functions (expressions) to improve MySQL Query. since ancient times, function indexes are not so complex. So how can this function be implemented in MySQL? Let's take a look at the concept of function indexing. Function indexes, also known as Expression indexes, are required to create indexes based on fields using specific functions (expressions) to improve query performance. The advantage of function indexing is to obtain the required data more accurately.


MySQL 5.7 provides a new feature, virtual columns, which can perfectly solve this problem.
Before introducing virtual columns, let's look at the examples of common indexes in MySQL.
Example table structure:

CREATE TABLE t1 (id INT ,rank INT, log_time DATETIME, nickname VARCHAR(64)) ENGINE INNODB;ALTER TABLE t1 ADD PRIMARY KEY (id), ADD KEY idx_rank (rank),ADD KEY idx_log_time (log_time);

In the sample table, I have added 5000 records:

mysql> select count(*) from t1;+----------+| count(*) |+----------+|     5000 |+----------+1 row in set (0.00 sec)

Let's assume that we are going to retrieve data from April 9, 2015. (The result is two records with IDs 95 and 3423, respectively .)

mysql> SELECT * FROM t1 WHERE DATE(log_time) = '2015-04-09'\G*************************** 1. row ***************************      id: 95    rank: 24log_time: 2015-04-09 05:53:13nickname: test*************************** 2. row ***************************      id: 3423    rank: 42log_time: 2015-04-09 02:55:38nickname: test2 rows in set (0.01 sec)

Let's take a look at the query plan of this statement.

mysql> explain SELECT * FROM t1 WHERE DATE(log_time) = '2015-04-09'\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 5000     filtered: 100.00        Extra: Using where1 row in set, 1 warning (0.00 sec)

We found that the TYPE is ALL, and the scan function is 5000. that is to say, this statement performs a full table scan. Although the log_time field is indexed but not used, what should I do at this time?
In MySQL, it is generally modified as follows:

mysql> SELECT * FROM t1 WHERE log_time >= '2015-04-09 00:00:00' AND log_time <='2015-04-10 00:00:00'\G*************************** 1. row ***************************      id: 3423    rank: 42log_time: 2015-04-09 02:55:38nickname: test*************************** 2. row ***************************      id: 95    rank: 24log_time: 2015-04-09 05:53:13nickname: test2 rows in set (0.00 sec)

The query results show that the result set is consistent. let's look at the query plan.

mysql> explain SELECT * FROM t1 WHERE log_time >= '2015-04-09 00:00:00' AND log_time <= '2015-04-10 00:00:00'\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: rangepossible_keys: idx_log_time          key: idx_log_time      key_len: 6          ref: NULL         rows: 2     filtered: 100.00        Extra: Using index condition1 row in set, 1 warning (0.00 sec)

We can see that this modified statement makes good use of the index idx_log_time.


Well, this is the solution in MySQL 5.6 and earlier versions. with the release of MySQL 5.7, the emergence of virtual columns makes this problem easier.
Modify the table structure as follows:

ALTER TABLE t1 ADD COLUMN log_date  DATE AS (DATE(log_Time)) stored, ADD KEY idx_log_date (log_date);

In this way, a new column is added to store the expression date (log_time) and a column index is added to it.


Then, the previous statement becomes as follows:

mysql> SELECT * FROM t1 WHERE log_date = '2015-04-09'\G*************************** 1. row ***************************      id: 95    rank: 24log_time: 2015-04-09 05:53:13nickname: testlog_date: 2015-04-09*************************** 2. row ***************************      id: 3423    rank: 42log_time: 2015-04-09 02:55:38nickname: testlog_date: 2015-04-092 rows in set (0.00 sec)

The result set after execution is consistent with the previous one.


Let's look at the query plan and find that the idx_log_date index column is well used.

mysql> explain  SELECT * FROM t1 WHERE log_date = '2015-04-09'\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: idx_log_date          key: idx_log_date      key_len: 4          ref: const         rows: 2     filtered: 100.00        Extra: NULL1 row in set, 1 warning (0.00 sec)

Through the above introduction, we can see that virtual columns are much easier to implement than the previous method. But here I still have to say a few words.
Although the usage of function indexes and SQL statements are simple to write, in most cases, it is a design defect that can only be said to have to do so, it increases the difficulty and complexity of O & M personnel. That's why MySQL didn't launch this similar feature until 5.7.