Analysis of several situations where MySQL index fails

1. Best left prefix principle - If multiple columns are indexed, the leftmost prefix principle must be followed. This means that the query starts from the leftmost column of the index and does not skip columns in the index.

Prerequisite: A composite index (username, password, age) has been added to the table

Analysis: The query lacks username, and the leftmost username in the query condition composite index is missing, which violates the best left prefix principle, causing the index to fail and become ALL, a full table scan.

Analysis: The query condition lacks username and password. The leftmost username and password in the query condition composite index are missing, which violates the best left prefix principle, causing the index to fail and become ALL, a full table scan.

Analysis: This query has only one username condition. According to the best left prefix principle, the index can be used, but only partially.

2. Do not perform any operations (calculations, functions, (automatic or manual) type conversion) on the index column, which will cause the index to fail and result in a full table scan.

Analysis: The first graph index column does not use functions, follows the left prefix principle, and can use the index. The second figure uses a function on the index column. Even if the left prefix principle is followed, the index is still invalid.

3. The storage engine cannot use the columns to the right of the range condition in the index, and the index becomes invalid after the range. (< ,> between and)

Analysis: All indexes in Figure 1 are used. In Figure 2, the index uses username and age, but username is retrieved using the index, while age focuses on index sorting. In this case, age is a range search, and the password index will be invalid.

4. When MySQL uses unequal (!= or <>), the index cannot be used, which will cause the index to fail.

5. Using is not null or is null in MySQL will cause the index to be unusable

Analysis: A normal index is created for the username column. The query contains is not null, and the resulting index is not effective.

6. In MySQL, if the like query starts with %, the index will become invalid and the whole table will be scanned, covering the index.

Analysis: A normal index is created for the username column, and a query is performed with a value beginning with %. As a result, the index becomes invalid and is overwritten.

7. In MySQL, string index will be invalid if it is not enclosed in single quotes. Correct way to write: select * from t_user where username = 'lujin';

8. In MySQL, if there is an or in the condition, it will not be used even if the condition contains an index (this is why you should try to use or as little as possible). If you want to use or and make the index effective, you can only add an index to each column in the or condition.

9. If MySQL uses a full table scan faster than an index, the index will not be used

Summarize

This is the end of this article about the analysis of MySQL index failure. For more relevant MySQL index failure content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!