A brief discussion on the invalidation or implicit conversion of MySQL integer and string indexes

Problem Overview

Today at work, the DBA suddenly found a piece of SQL, indicating that the SQL had implicit conversion and did not use the index. After checking, we found that it was a varchar type field. We used conditions to pass in a numeric value. Due to concerns about violating the confidentiality agreement, I will not post the picture here. Let me reproduce a similar situation for everyone to see.

Problem Reproduction

First, we create a user table test_user, in which USER_ID is set to varchar type and a unique index is added for the sake of effect.

CREATE TABLE test_user (
  ID int(11) NOT NULL AUTO_INCREMENT,
  USER_ID varchar(11) DEFAULT NULL COMMENT 'User account',
  USER_NAME varchar(255) DEFAULT NULL COMMENT 'User name',
  AGE int(5) DEFAULT NULL COMMENT 'Age',
  COMMENT varchar(255) DEFAULT NULL COMMENT 'Introduction',
  PRIMARY KEY (ID)
  UNIQUE KEY UNIQUE_USER_ID (USER_ID) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

The table data is as follows (the data still uses the same data as the last MySQL article MySQL uses UNION to connect two queries and sorting fails, but please note that the table structure is different.)

Next we execute the following sql

EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111;

The explanations given are found to be as follows:

We put the condition in quotation marks and explain it as follows:

EXPLAIN SELECT * FROM test_user WHERE USER_ID = '111';

At this time, we found that the varchar type field uses the index when it is queried as a string, but does not use the index when it is queried as a numeric type.

Problem extension

So the question is, if the field is an integer and has an index, will the index not be used when querying with a string? Practice makes perfect, let’s continue testing it.

-- Change the type of USER_ID to integer CREATE TABLE test_user (
  ID int(11) NOT NULL AUTO_INCREMENT,
  USER_ID int(11) DEFAULT NULL COMMENT 'User account',
  USER_NAME varchar(255) DEFAULT NULL COMMENT 'User name',
  AGE int(5) DEFAULT NULL COMMENT 'Age',
  COMMENT varchar(255) DEFAULT NULL COMMENT 'Introduction',
  PRIMARY KEY (ID),
  UNIQUE KEY UNIQUE_USER_ID (USER_ID) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111;

EXPLAIN SELECT * FROM test_user WHERE USER_ID = '111';

After executing the above two statements, we found that int type fields will be indexed regardless of whether they are queried as strings or numerical values.

in conclusion

1. When the field we use is a numeric type, adding quotes or not adding quotes (single quotes and double quotes in SQL have the same effect) does not affect the use of the index
2. When our field is of string type, the index cannot be used for queries without quotes, and the index can be used normally for queries with quotes

To sum up, I think it is best to add quotation marks when writing SQL in the future to avoid the situation where string types are not indexed. The deeper principles need to be explored further. If you have any opinions, please discuss them.

This is the end of this article about the invalidation or implicit conversion of MySql integer index and string index. For more information about the invalidation of MySql integer index and string index, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!