What is the reason why character coding does not go through the index?

This article mainly explains "what is the reason why character coding does not go to the index". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. let's study and learn "what is the reason why character coding does not go to the index"?

SQL:

SELECT * FROM oc_order oo join orders_detail od ON oo.order_id = od.order_id

But in a moment (nearly 20 minutes), I found out the reason, pretended to be a master, and said word by word, "this is the problem of implicit conversion of character sets."

Positioning problem 1. First execute explain to view the execution plan explainSELECT * FROM oc_order oo join orders_detail od ON oo.order_id = od.order_id

It is true that the driven table orders_detail did not leave the index.

The first table is the driven table, followed by the driven table, which will take the data from the driver board as parameters and query the matching records to the driven table.

-non-technical terms, Wen Anshi 20210223

two。 View the index SHOW INDEX FROM test.orders_detail of the driven table

3. View the field character set SELECT COLUMN_NAME,character_set_name,collation_name FROM INformation_ schema.`COLUMNS` WHERE TABLE_NAME='oc_order' AND COLUMN_NAME='order_id'

SELECT COLUMN_NAME,character_set_name,collation_name FROM INformation_ schema.`COLUMNS` WHERE TABLE_NAME='orders_detail'AND COLUMN_NAME='order_id'

Mysql character set description

Utf8m4 is a utf8 superset, utf8, compared with utf8mb4, utf8 will be converted to utf8mb4.

Verification

Adjust the SQL statement to cast the order_id of oc_orders to utf8.

ExplainSELECT * FROM oc_order oo join orders_detail od ON convert (oo.order_id USING UTF8) = od.order_id

Check the execution plan and do go to the index

Solution adjustment SQL statement SELECT * FROM oc_order oo join orders_detail od ON convert (oo.order_id USING UTF8) = od.order_id adjust character set consistent, it is recommended to summarize

There are three situations where you may not take the index.

Perform functional operations on index fields

Implicit type conversion, strings are compared with numbers, strings are converted to numbers

Implicit character set conversion, utf8m4 is a utf8 superset, utf8, compared with utf8mb4, utf8 will be converted to utf8mb4.

Thank you for your reading. The above is the content of "what is the reason for the indexing of character coding?" after the study of this article, I believe you have a deeper understanding of what the reason for the indexing of character coding is. The specific use of the situation also needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!