Will using functions in MySQL invalidate indexes?

Article directory

1. Preparation
2. ChatGPT’s answer
3. Practical proof
- SQL1
- SQL2
- SQL3
- SQL4
- SQL5
4. Summary

1. Preparation

First create the library table we want to test

CREATE TABLE `lianhe_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_nameagenumber` (`name`,`age`,`number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=200001 DEFAULT CHARSET=utf8;

Mainly established the primary key index id and joint index idx_nameagenumber, and created 100,000 pieces of data.
Generally, functions can use columns in the select clause or columns in the where clause. Let’s analyze these two situations below.

2. ChatGPT’s answer

First let’s ask about ChatGPT

In MySQL, using functions as select fields usually does not go through the index. This is because the use of functions will cause MySQL to be unable to directly match the index key value. Instead, it needs to perform function calculations on all records to determine the result. However, in some special cases, MySQL may take advantage of indexes through certain optimization strategies. For example, in some cases, covering indexes can be used to avoid accessing table data.
For the where field, using a function as a condition usually does not use the index. For the same reason, the use of functions will cause MySQL to be unable to directly match the index key value. Instead, it needs to perform function calculations on all records to determine the result. Therefore, if you want the where field to be indexed, it is recommended to avoid using functions as much as possible.
The above is ChatGPT’s answer. Is the answer correct? Keep the answer first, let’s practice it next~

3. Practical proof

We mainly analyze the following five SQLs. First, guess whether they will be indexed?

SELECT id,age,number,UPPER(name),create_time FROM lianhe_index where name='Lin Wei' and age=69 and number=76291;
SELECT id,age,number,name,create_time FROM lianhe_index where UPPER(name)='伟A' and age=69 and number=76291;
SELECT id,age,number,name,create_time FROM lianhe_index where name=UPPER(name) and age=69 and number=76291;
SELECT id,age,number,name,create_time FROM lianhe_index where UPPER(name)='伟A' and age=69 and number=76291 and DATE(create_time)="2022-11-11";
SELECT id,age,number,name,create_time FROM lianhe_index where name=UPPER(name) and age=69 and number=76291 and DATE(create_time)="2022-11-11";

The execution plan is as follows, is it the same as you thought?

mysql> EXPLAIN SELECT id,age,number,UPPER(name),create_time FROM lianhe_index where name='Lin Wei' and age=69 and number=76291;
EXPLAIN SELECT id,age,number,name,create_time FROM lianhe_index where UPPER(name)='伟A' and age=69 and number=76291;
EXPLAIN SELECT id,age,number,name,create_time FROM lianhe_index where name=UPPER(name) and age=69 and number=76291;
EXPLAIN SELECT id,age,number,name,create_time FROM lianhe_index where UPPER(name)='伟A' and age=69 and number=76291 and DATE(create_time)="2022-11-11";
EXPLAIN SELECT id,age,number,name,create_time FROM lianhe_index where name=UPPER(name) and age=69 and number=76291 and DATE(create_time)="2022-11-11";
 + ---- + ------------- + ------------- + ------------ + -- ---- + ------------------- + ------------------- + ----- ---- + ------------------- + ------ + ---------- + ------- +
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 + ---- + ------------- + ------------- + ------------ + -- ---- + ------------------- + ------------------- + ----- ---- + ------------------- + ------ + ---------- + ------- +
| 1 | SIMPLE | lianhe_index | NULL | ref | idx_nameagenumber | idx_nameagenumber | 778 | const,const,const | 1 | 100.00 | NULL |
 + ---- + ------------- + ------------- + ------------ + -- ---- + ------------------- + ------------------- + ----- ---- + ------------------- + ------ + ---------- + ------- +
1 row in set (0.02 sec)

 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| 1 | SIMPLE | lianhe_index | NULL | ALL | NULL | NULL | NULL | NULL | 99954 | 1.00 | Using where |
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
1 row in set (0.02 sec)

 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| 1 | SIMPLE | lianhe_index | NULL | ALL | NULL | NULL | NULL | NULL | 99954 | 0.10 | Using where |
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
1 row in set (0.02 sec)

 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| 1 | SIMPLE | lianhe_index | NULL | ALL | NULL | NULL | NULL | NULL | 99954 | 1.00 | Using where |
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
1 row in set (0.02 sec)

 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| 1 | SIMPLE | lianhe_index | NULL | ALL | NULL | NULL | NULL | NULL | 99954 | 0.10 | Using where |
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
1 row in set (0.02 sec)

Let’s analyze each one below

SQL1

SELECT id,age,number,UPPER(name),create_time FROM lianhe_index where name='Lin Wei' and age=69 and number=76291;
 + ---- + ------------- + ------------- + ------------ + -- ---- + ------------------- + ------------------- + ----- ---- + ------------------- + ------ + ---------- + ------- +
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 + ---- + ------------- + ------------- + ------------ + -- ---- + ------------------- + ------------------- + ----- ---- + ------------------- + ------ + ---------- + ------- +
| 1 | SIMPLE | lianhe_index | NULL | ref | idx_nameagenumber | idx_nameagenumber | 778 | const,const,const | 1 | 100.00 | NULL |
 + ---- + ------------- + ------------- + ------------ + -- ---- + ------------------- + ------------------- + ----- ---- + ------------------- + ------ + ---------- + ------- +

It can be seen that the key in the execution plan uses the combined index idx_nameagenumber, and the number of rows scanned is rows=1, so the index takes effect.
Although RIGHT(name,1) is a function, it is applied in the where clause to the column name that is already in the index, so MySQL can use the index to locate matching rows

If we do not query create_time, we will also find that Using index appears in Extra in the execution plan, indicating that a covering index is used.

SELECT id,age,number,UPPER(name) FROM lianhe_index where name='Lin Wei' and age=69 and number=76291;
 + ---- + ------------- + ------------- + ------------ + -- ---- + ------------------- + ------------------- + ----- ---- + ------------------- + ------ + ---------- + ------- ------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 + ---- + ------------- + ------------- + ------------ + -- ---- + ------------------- + ------------------- + ----- ---- + ------------------- + ------ + ---------- + ------- ------ +
| 1 | SIMPLE | lianhe_index | NULL | ref | idx_nameagenumber | idx_nameagenumber | 778 | const,const,const | 1 | 100.00 | Using index |
 + ---- + ------------- + ------------- + ------------ + -- ---- + ------------------- + ------------------- + ----- ---- + ------------------- + ------ + ---------- + ------- ------ +

SQL2

SELECT id,age,number,name,create_time FROM lianhe_index where UPPER(name)='伟A' and age=69 and number=76291;
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| 1 | SIMPLE | lianhe_index | NULL | ALL | NULL | NULL | NULL | NULL | 99954 | 1.00 | Using where |
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +

As you can see, the key=null, rows=99954 in the execution plan, and the Extra column are displayed as “Using where”, which means that the query uses the where clause for filtering, but does not use the index.

If we do not query create_time, we can find that in the execution plan, the “possible_keys” column shows “NULL”, which means that there are no other available indexes. But “idx_nameagenumber” is shown in the “key” column, which means that the index was selected for executing the query. In addition, the Extra column shows “Using where; Using index”, which means that the query uses a where clause to filter and an index to locate matching rows. So I used an index, but the effect was not good. I personally think there is something wrong with the execution plan.

SELECT id,age,number,name FROM lianhe_index where UPPER(name)='伟A' and age=69 and number=76291;
 + ---- + ------------- + ------------- + ------------ + -- ----- + --------------- + ------------------- + -------- - + ------ + ------- + ---------- + ----------------------- ---- +
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 + ---- + ------------- + ------------- + ------------ + -- ----- + --------------- + ------------------- + -------- - + ------ + ------- + ---------- + ----------------------- ---- +
| 1 | SIMPLE | lianhe_index | NULL | index | NULL | idx_nameagenumber | 778 | NULL | 99954 | 1.00 | Using where; Using index |
 + ---- + ------------- + ------------- + ------------ + -- ----- + --------------- + ------------------- + -------- - + ------ + ------- + ---------- + ----------------------- ---- +

SQL3

SELECT id,age,number,name,create_time FROM lianhe_index where name=UPPER(name) and age=69 and number=76291;
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| 1 | SIMPLE | lianhe_index | NULL | ALL | NULL | NULL | NULL | NULL | 99954 | 0.10 | Using where |

SQL4

SELECT id,age,number,name,create_time FROM lianhe_index where UPPER(name)='伟A' and age=69 and number=76291 and DATE(create_time)="2022-11-11";
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| 1 | SIMPLE | lianhe_index | NULL | ALL | NULL | NULL | NULL | NULL | 99954 | 1.00 | Using where |
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +

SQL5

SELECT id,age,number,name,create_time FROM lianhe_index where name=UPPER(name) and age=69 and number=76291 and DATE(create_time)="2022-11-11";
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 + ---- + ------------- + ------------- + ------------ + -- ---- + --------------- + ------ + --------- + ------ + ----- -- + ---------- + ------------- +
| 1 | SIMPLE | lianhe_index | NULL | ALL | NULL | NULL | NULL | NULL | 99954 | 0.10 | Using where |

4. Summary

When the function is applied to the index column in the select clause, the index still takes effect; when the function is applied to the index column in the where clause, MySQL may not be able to use the index to speed up the query. Although there are some cases, the execution plan shows that keys are used The index is installed, but the actual effect is not good, it can be regarded as the index is invalid.