Introduction
For relational databases, the design of tables and SQL is written are particularly crucial. It wouldn’t be an exaggeration to say that they account for 90% of performance. So this time, specifically targeting these two major knowledge areas, we’ll conduct a detailed analysis for you, peeling back the layers.
This Series uses plain and understandable language and selects a large number of examples to elaborate on the subtleties for you.
🧑💻 Target audience:
- DBA
- Database developers
- Students
We will use MySQL as the demonstration database.
1. Mastering MySQL Functional Indexes
Functional indexes in MySQL are based on fields combined with functions, operators, or expressions. Unlike regular indexes, they leverage generated columns to store computed results without persisting the intermediate data, enhancing query performance significantly.
2. Use Cases for Functional Indexes
One of the most common use cases for functional indexes is handling date-related queries. For example, when you need to query parts of a datetime field. Consider a table t1 with a datetime field log_time:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| <localhost|mysql>show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`log_time` datetime(6) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_log_time` (`log_time`)
) ENGINE=InnoDB AUTO_INCREMENT=524268 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
<localhost|mysql>select count(*) from t1;
+----------+
| count(*) |
+----------+
| 393216 |
+----------+
1 row in set (0.07 sec)
|
A query filtering by the date part of log_time initially performs poorly:
1
2
3
4
5
6
7
8
| -- SQL 1
<localhost|mysql>select * from t1 where date(log_time) = '2100-02-02';
+--------+----------------------------+
| id | log_time |
+--------+----------------------------+
| 524267 | 2100-02-02 08:09:09.123972 |
+--------+----------------------------+
1 row in set (0.09 sec)
|
The execution plan reveals a full table scan despite using an index:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| <localhost|mysql>explain select * from t1 where date(log_time) = '2100-02-02'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: index
possible_keys: NULL
key: idx_log_time
key_len: 9
ref: NULL
rows: 392413
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
|
Creating a functional index on date(log_time) improves performance dramatically:
1
2
3
| <localhost|mysql>alter table t1 add key idx_func_index_1((date(log_time)));
Query OK, 0 rows affected (2.76 sec)
Records: 0 Duplicates: 0 Warnings: 0
|
The query now executes instantly:
1
2
3
4
5
6
7
| <localhost|mysql>select * from t1 where date(log_time) = '2100-02-02';
+--------+----------------------------+
| id | log_time |
+--------+----------------------------+
| 524267 | 2100-02-02 08:09:09.123972 |
+--------+----------------------------+
1 row in set (0.00 sec)
|
The execution plan confirms optimal usage of the functional index:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| <localhost|mysql>explain select * from t1 where date(log_time) = '2100-02-02'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: idx_func_index_1
key: idx_func_index_1
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
|
3. Handling JSON Fields with Functional Indexes
When working with JSON fields, direct indexing using operators like ->> may fail. Consider the following example:
1
2
3
4
5
| <localhost|mysql>create table t2 (id int primary key, r1 json);
Query OK, 0 rows affected (0.09 sec)
<localhost|mysql>alter table t2 add key idx_func_index_2((r1->>'$.x'));
ERROR 3757 (HY000): Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.
|
To resolve this, explicitly cast the JSON value to the appropriate type:
1
2
3
| <localhost|mysql>alter table t2 add key idx_func_index_2((cast(r1->>'$.x' as char(1)) collate utf8mb4_bin));
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
|
3. Can Functional Indexes Replace Prefix Indexes?
While functional indexes are powerful, they cannot universally replace prefix indexes. Functional indexes require exact matches to the defined function for query optimization. For example, consider a table t3 with various indexes:
1
2
3
4
5
6
7
8
9
10
11
12
13
| <localhost|mysql>show create table t3\G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`r1` char(36) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `idx_r1_prefix` (`r1`(8)),
KEY `idx_func_index_3` ((left(`r1`,8))),
KEY `idx_func_index_4` ((substr(`r1`,1,8)))
) ENGINE=InnoDB AUTO_INCREMENT=249 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
|
Different SQL statements may use different indexes:
1
2
3
4
5
6
7
8
| -- SQL 3
select * from t3 where r1 like 'de45c7d9%';
-- SQL 4
select * from t3 where left(r1,8) ='de45c7d9';
-- SQL 5
select * from t3 where substring(r1,1,8) ='de45c7d9';
|
Each query uses a different index:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
| <localhost|mysql>explain select * from t3 where r1 like 'de45c7d9%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t3
partitions: NULL
type: range
possible_keys: idx_r1_prefix
key: idx_r1_prefix
key_len: 33
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
<localhost|mysql>explain select * from t3 where left(r1,8) ='de45c7d9'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t3
partitions: NULL
type: ref
possible_keys: idx_func_index_3
key: idx_func_index_3
key_len: 35
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
<localhost|mysql>explain select * from t3 where substring(r1,1,8) ='de45c7d9'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t3
partitions: NULL
type: ref
possible_keys: idx_func_index_4
key: idx_func_index_4
key_len: 35
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
|
4. Implementing Functional Indexes in Older MySQL Versions
For MySQL versions prior to 8.0.13, you can simulate functional indexes using generated columns. In MySQL 5.7, create a virtual generated column and index it:
1
2
| ALTER TABLE your_table ADD (generated_column datatype) GENERATED ALWAYS AS (function(column)) VIRTUAL,
ADD INDEX idx_generated_column (generated_column);
|
In older versions like MySQL 5.6 or 5.5, you need to manually manage a redundant column:
- Add a new column to store the computed value
- Update this column whenever the source data changes
- Create an index on this new column
This approach requires careful planning and maintenance but can significantly improve query performance in legacy systems.
👋 See you in the next lesson.
Recommended reading
What is SQLFlash?
SQLFlash is your AI-powered SQL Optimization Partner.
Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.
How to use SQLFlash in a database?
