Impact of type conversion on MySQL index selection _ MySQL

Impact of type conversion on MySQL index selection bitsCN.com

I encountered several cases where MySQL did not use the expected index. after reading some documents, I found that the impact of MySQL type conversion on index selection is not small.

For example, there is a MySQL table:

Create table 'indextest '(
'Id' int (10) unsigned not null AUTO_INCREMENT,
'Name' varchar (10) default null,
'Age' tinyint (3) unsigned not null default '0 ′,
'Create _ time' timestamp not null default CURRENT_TIMESTAMP,
Primary key ('id '),
KEY 'idx _ name' ('name '),
KEY 'idx _ age' ('age '),
KEY 'idx _ create' ('create _ time ')
) ENGINE = InnoDB AUTO_INCREMENT = 9 default charset = latin1

Name is an indexed varchar field. The data in the table is as follows:

+ -- + --- + -- + ------- +
| Id | name | age | create_time |
+ -- + --- + -- + ------- +
| 1 | hello | 10 | 20:00:00 |
| 2 | world | 20 | 20:00:00 |
| 3 | 111222 | 30 | 20:00:00 |
| 4 | wow | 40 | 20:00:00 |
+ -- + --- + -- + ------- +

Use the string '200' as the parameter to query the name field. the Execution Plan uses the index idx_name on the name field as expected:

Mysql [localhost] {msandbox} (test)> explain select age from
-> Indextest where name = '000000'/G
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: indextest
Type: ref
Possible_keys: idx_name
Key: idx_name
Key_len: 13
Ref: const
Rows: 1
Extra: Using where
1 row in set (0.00 sec)

When a number is used as a parameter to query the name field, explain indicates that this is a full table scan:

Mysql [localhost] {msandbox} (test)> explain select age from
-> Indextest where name = 111222/G
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: indextest
Type: ALL
Possible_keys: idx_name
Key: NULL
Key_len: NULL
Ref: NULL
Rows: 4
Extra: Using where
1 row in set (0.00 sec)

The reason is that, when comparing text fields with numbers, MySQL needs to perform implicit type conversion to compare different types. The results are the same as described in the preceding example.

MySQL documentation (Type Conversion in Expression Evaluation) mentions that during comparison, necessary Type Conversion will be performed according to such rules:

When at least one of the two parameters is NULL, the comparison result is also NULL. The exception is that when two NULL values are compared, 1 is returned. no type conversion is required in both cases.
Both parameters are strings and will be compared according to strings without type conversion.
Both parameters are integers, which are compared by integers without type conversion.
When the hexadecimal value is compared with a non-numeric value, it is treated as a binary string. when compared with a number, it is processed according to the following rule.
One parameter is TIMESTAMP or DATETIME, and the other is a constant, the constant will be converted to timestamp
One parameter is of the decimal type. if another parameter is of the decimal or integer type, the integer is converted to the decimal type for comparison. if the other parameter is of the floating point type, converts decimal to a floating point number for comparison.
In all other cases, both parameters are converted to floating-point numbers for comparison.
For example:

Mysql [localhost] {msandbox} (test)> SELECT '000000' =
-> 18015376320243459;
+ -------------- +
| '000000' = 18015376320243459 |
+ -------------- +
| 0 |
+ -------------- +
1 row in set (0.00 sec)

Mysql [localhost] {msandbox} (test)> SELECT '000000' + 0;
+ --------- +
| '000000' + 0 |
+ --------- +
| 1.80153763202435e + 16 |
+ --------- +
1 row in set (0.00 sec)
Mysql [localhost] {msandbox} (test)> SELECT
-> Cast ('000000' as unsigned) = 18015376320243459;
+ -------------------- +
| Cast ('000000' as unsigned) = 18015376320243459 |
+ -------------------- +
| 1 |
+ -------------------- +
1 row in set (0.00 sec)

Because of the floating point precision (53 bits) problem, and MySQL converts a string to a floating point number and converts an integer to a floating point number using different methods, the string '123' is not equal to the integer 18015376320243459, to avoid the precision problem caused by implicit floating point conversion, you can explicitly use cast for type conversion and convert the string to an integer.

According to these rules, for the above example, the value of the name field and the query parameter '000000' will be converted to a floating point number for comparison, many texts can be converted to values equal to 111222, for example, '1222aabb ', '1222923', and '121222aabb. 2e1 ', so MySQL cannot effectively use indexes, which degrades to index scanning or even full table scanning.

In turn, if you use a string as the query parameter to compare and query a numeric field, MySQL can effectively use the index:

Mysql [localhost] {msandbox} (test)> explain select name from
-> Indextest where age = '30'/G
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: indextest
Type: ref
Possible_keys: idx_age
Key: idx_age
Key_len: 1
Ref: const
Rows: 1
Extra:
1 row in set (0.00 sec)

The reason is that MySQL can convert the query parameter '30' to the determined value 30, and then quickly find the same value in the index.

In addition, using a function to explicitly convert the index field or calculate the index will make MySQL unable to use the index:

Mysql [localhost] {msandbox} (test)> explain select name from
-> Indextest where cast (age as unsigned) = 30/G
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: indextest
Type: ALL
Possible_keys: NULL
Key: NULL
Key_len: NULL
Ref: NULL
Rows: 4
Extra: Using where
1 row in set (0.00 sec)

As shown above, explicit type conversion using the cast function on age will invalidate the index. of course, this is rarely written in actual code, however, there are many operations similar to the following on the time field:

Mysql [localhost] {msandbox} (test)> explain select * from
-> Indextest where date (create_time) = '2017-02-02 '/G
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: indextest
Type: ALL
Possible_keys: NULL
Key: NULL
Key_len: NULL
Ref: NULL
Rows: 4
Extra: Using where
1 row in set (0.00 sec)

In this example, we want to find the record whose create_time is. we can use a work und to avoid using the index Field to effectively use the index:

Mysql [localhost] {msandbox} (test)> explain select * from
-> Indextest where create_time between '2017-02-02 'and '2017-02-03'/G
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table: indextest
Type: range
Possible_keys: idx_create
Key: idx_create
Key_len: 4
Ref: NULL
Rows: 1
Extra: Using where
1 row in set (0.00 sec)

How of MySQL... The series of documents are worth reading, for example:

• How MySQL Uses Indexes
• How MySQL Uses Memory
• How MySQL Uses Internal Temporary Tables
• How to configure with Deadlocks
• How MySQL Opens and Closes Tables
• How MySQL Uses Threads for Client Connections
• How to Determine What is Causing a Problem

Documents of great open-source software always need to be read repeatedly before they can be understood and correctly applied. the brilliance of RTFM and RTFS is infinite.

BitsCN.com