What are the situations in which mysql index fails?

Instances of index failure: 1. The like query starts with "%"; 2. The index is not used before and after the or statement; 3. The first column index is not used in the combined index; 4. Used on the index column "IS NULL" or "IS NOT NULL" operation; 5. Use "not", "<>", "!=", etc. on the index field.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Index is a special database structure, which is composed of one or more columns in the data table. It can be used to quickly query records with a specific value in the data table. This section will explain in detail the meaning, function, advantages and disadvantages of indexes.

Through the index, when querying data, you do not need to read all the information in the record, but only query the index column. Otherwise, the database system will read all information of each record for matching.

The index can be compared to the phonetic sequence of the Xinhua Dictionary. For example, if you want to look up the word "ku", if you don't use phonetic sequence, you need to find it page by page in the 400 pages of the dictionary. However, if you extract the pinyin to form a phonetic sequence, you only need to look it up directly from the phonetic table of more than 10 pages. This can save a lot of time.

Therefore, using indexes can greatly improve the query speed of the database and effectively improve the performance of the database system.

Several situations in which index query fails:

1. Like starts with %, the index is invalid; when like When there is no % in the prefix and % in the suffix, the index is valid.

2. The index is not used at the same time before and after the or statement.

When only one of the left and right query fields of or is an index, the index will be invalid. It will only take effect when both the left and right query fields of or are indexes

#3. Combined index, instead of using the first column index, the index will be invalid.

4. If the column type is a string, the data must be quoted in quotes, otherwise the index will not be used

Implicit conversion occurs in data type. If varchar is not enclosed in single quotes, it may be automatically converted to int type, invalidating the index and causing a full table scan.

5. Use the IS NULL or IS NOT NULL operation on the index column.

Indices do not index null values, so such operations cannot use indexes and can be handled in other ways, such as: numeric type, judge greater than 0, string type set a default value, judge It can be equal to the default value. (This is wrong!)

Explanation of the above error:

I will re- Create an emp table

Create a new index

View the index

Execute SQL statement

You can find that the index is used

Summary: Using the IS NULL or IS NOT NULL operation on the index column, the index does not necessarily become invalid! ! !

6. Use not, <>, != on the index field.

The inequality operator will never use the index, so its processing will only produce a full table scan. Optimization method: change key<>0 to key>0 or key<0.

7. Perform calculation operations on the index fields and use functions on the fields. (The index is emp(ename,empno,sal))

##8. When the full table scan speed is When the index speed is fast, mysql will use a full table scan, and the index will fail at this time.

If mysql estimates that using a full table scan is faster than using an index, then the index will not be used

[Related recommendations:

mysql video tutorial]

The above is the detailed content of What are the situations in which mysql index fails?. For more information, please follow other related articles on the PHP Chinese website!