Situation causing MySQL index failure

Several situations and code examples of MySQL index failure

Introduction:
In the MySQL database, the index is one of the important factors to improve query performance. However, sometimes we find that the index does not play the expected role, query performance is not improved, and even causes the query to become slower. The reason is probably that the index is invalid. This article will introduce several common situations that cause MySQL index failure and give corresponding code examples.

1. Use functions or expressions to perform operations on index columns
If function calls or expression operations are performed on index columns in the query statement, the index will become invalid and MySQL will not be able to use the index for fast processing. Inquire. The following is an example:

-- 创建表
CREATE TABLE `user_address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 查询示例，索引失效
SELECT * FROM `user_address` WHERE YEAR(`address`) = 2022;

In the above code, due to the use of the YEAR function for the address column, MySQL cannot use the index idx_user_id, resulting in index failure and reduced query efficiency.

2. Using functions on index columns
Using functions on index columns will also cause index failure, such as the following example:

-- 查询示例，索引失效
SELECT * FROM `user_address` WHERE LEFT(`address`, 5) = 'China';

In the above code, due to the ## The #address column uses the LEFT function, which also prevents MySQL from using the index idx_user_id, causing the index to fail.

When using the LIKE operator for fuzzy query, if it does not start with the % wildcard character, the index will become invalid. An example is as follows:

-- 查询示例，索引失效
SELECT * FROM `user_address` WHERE `address` LIKE 'Shanghai%';

idx_user_id, and the query performance decreases.

Use the OR keyword in query conditions. If the columns in each OR clause do not have an index, the index will fail. The example is as follows:

-- 查询示例，索引失效
SELECT * FROM `user_address` WHERE `user_id` = 1 OR `address` = 'Shanghai';

user_id and address each have their own index, but in the two conditions connected by the OR keyword, respectively Different indexes are used, causing the index to become invalid.

When using the MySQL database, we need to avoid the above scenarios that lead to index failure. When writing SQL statements, you should try to avoid performing function or expression operations on index columns, and use appropriate operators and connections to construct query conditions to ensure that the index can be effectively used in query operations and improve query performance.

MySQL official documentation (https://dev.mysql.com/doc/)

The above is the detailed content of Situation causing MySQL index failure. For more information, please follow other related articles on the PHP Chinese website!