A brief discussion on several situations where adding indexes to MySQL does not take effect

Adding indexes can improve query efficiency.

Adding an index means adding an index file, which stores the address of the data, similar to the directory of our document. During the search process, you do not need to search from the contents of the book, but can directly search according to the page number corresponding to the directory. Indexes are searched based on addresses.
Create an index. There are many data structures used by the index. Common ones are B-tree, hash, etc. The default database index used by MySQL is innerDB, and the index structure of innerDB is B-tree.
But in what situations will adding indexes fail to achieve the expected results during use? Here are some common situations:
Assume that name age address have been indexed. The index names are index_name, index_age, and index_address.

Use explain to view the SQL execution plan

Execution plan type

Indicates the way MySQL finds the required row in the table, also known as the "access type". Common types are as follows:

ALL, index, range, ref, eq_ref, const, system, NULL
ALL: Full Table Scan, MySQL will traverse the entire table to find matching rows
index: Full Index Scan. The difference between index and ALL is that the index type only traverses the index tree.
range: Index range scan, the scan of the index starts at a certain point and returns the rows that match the value range. The obvious index range scan is a query with a between or where clause with <, >. When MySQL uses an index to find a range of values, such as IN() and OR lists, it will also display a range scan, of course, there are differences in performance.
ref: Use a non-unique index scan or a prefix scan of a unique index to return rows that match a single value
eq_ref: Similar to ref, the difference is that the index used is a unique index. For each index key value, there is only one matching record in the table. In simple terms, the primary key or unique key is used as the join condition in multi-table joins.

1. Use or to query multiple columns, type=all

2. Use fuzzy query, type=all

3. Operations on indexed fields

4. If the column type is a string, it needs to be quoted. The name field is a string type

Without quotes type=all

There are quotation marks tye=ref

First of all, MySQL has a type conversion rule that converts "characters to numbers". The above SQL contains operations:
explain SELECT name,age,address FROM user where cast(name as signed)= 10;

5. Reverse query distinction

(not, not in, not like, <>, !=, !>, !<) Use index effects

Other situations

1. The associated fields in the joint index do not use the same character set;
2. The first index field is not used in the joint index query, that is, the leftmost principle is not met;
3. The amount of data itself is small, and MySQL will determine whether an index is needed;

This concludes this article on several situations in which adding an index to MySQL does not work. For more information about adding an index to MySQL not working, please search for previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!