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%';In the above code, because the LIKE operator does not start with the % wildcard character, MySQL cannot use the index
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';In the above code, since the two columns
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!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1796
16
1746
56
1593
29
1475
72
267
587
Steps to add and delete fields to MySQL tables
Apr 29, 2025 pm 04:15 PM
In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.
How to solve SQL parsing problem? Use greenlion/php-sql-parser!
Apr 17, 2025 pm 09:15 PM
When developing a project that requires parsing SQL statements, I encountered a tricky problem: how to efficiently parse MySQL's SQL statements and extract the key information. After trying many methods, I found that the greenlion/php-sql-parser library can perfectly solve my needs.
How to create Mysql database using phpMyadmin
Apr 10, 2025 pm 10:48 PM
phpMyAdmin can be used to create databases in PHP projects. The specific steps are as follows: Log in to phpMyAdmin and click the "New" button. Enter the name of the database you want to create, and note that it complies with the MySQL naming rules. Set character sets, such as UTF-8, to avoid garbled problems.
In JDBC's PreparedStatement, why do you need to use a specific parameter type setting method instead of the general setObject method?
Apr 19, 2025 pm 08:00 PM
JDBC...
phpMyAdmin comprehensive use guide
Apr 10, 2025 pm 10:42 PM
phpMyAdmin is not just a database management tool, it can give you a deep understanding of MySQL and improve programming skills. Core functions include CRUD and SQL query execution, and it is crucial to understand the principles of SQL statements. Advanced tips include exporting/importing data and permission management, requiring a deep security understanding. Potential issues include SQL injection, and the solution is parameterized queries and backups. Performance optimization involves SQL statement optimization and index usage. Best practices emphasize code specifications, security practices, and regular backups.
How does MySQL index cardinality affect query performance?
Apr 14, 2025 am 12:18 AM
MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.
centos postgresql resource monitoring
Apr 14, 2025 pm 05:57 PM
Detailed explanation of PostgreSQL database resource monitoring scheme under CentOS system This article introduces a variety of methods to monitor PostgreSQL database resources on CentOS system, helping you to discover and solve potential performance problems in a timely manner. 1. Use PostgreSQL built-in tools and views PostgreSQL comes with rich tools and views, which can be directly used for performance and status monitoring: pg_stat_activity: View the currently active connection and query information. pg_stat_statements: Collect SQL statement statistics and analyze query performance bottlenecks. pg_stat_database: provides database-level statistics, such as transaction count, cache hit
How to develop a complete Python Web application?
May 23, 2025 pm 10:39 PM
To develop a complete Python Web application, follow these steps: 1. Choose the appropriate framework, such as Django or Flask. 2. Integrate databases and use ORMs such as SQLAlchemy. 3. Design the front-end and use Vue or React. 4. Perform the test, use pytest or unittest. 5. Deploy applications, use Docker and platforms such as Heroku or AWS. Through these steps, powerful and efficient web applications can be built.
