MySQL SQL optimized string-index implicit conversion

Before the user is very puzzled: The SQL statement is very simple, is the select * from test_1 where user_id=1 this type, and user_id already indexed, how is the query very slow?

Table Structure of the test_1:

CREATE TABLE ' test_1 ' (

' id ' int (one) not NULL auto_increment,

' user_id ' varchar (+) not NULL,

' Name ' varchar (() DEFAULT NULL,

PRIMARY KEY (' id '),

KEY ' idx_user_id ' (' user_id ')

) Engine=innodb auto_increment=4 DEFAULT Charset=utf8

Looking at the execution plan, you can see that a full table scan has been performed, and the index of USER_ID is not used.

Mysql> Explain select * from test_1 where user_id=1;

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

| 1 | Simple | test_1 | All | idx_user_id | NULL | NULL |    NULL | 3 | Using where |

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

1 row in Set (0.01 sec)

Look closely at the table structure, user_id field type: ' user_id ' varchar (+) not NULL,

When the user enters int, there is an implicit conversion problem. Implicit conversions cause full-table scans.

Change the input to a string type and execute the plan as follows, and it will be quick.

Mysql> Explain select * from test_1 where user_id= ' 1 ';

+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------------+

| 1 | Simple | test_1 | Ref | idx_user_id | idx_user_id | 92 |    Const | 1 | Using where |

+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------------+

1 row in Set (0.00 sec)

In addition, it is important to note that:

0001 of the numeric type is equivalent to 1

0001 and 1 of strings are not equivalent

Mysql> select * from Test_1;

+----+---------+------+

| ID | user_id | name |

+----+---------+------+

| 1 | 0001 | Kate |

| 2 | 1101 | Jim |

| 3 | 1 | Jim |

+----+---------+------+

3 rows in Set (0.01 sec)

Mysql> SELECT * from test_1 where user_id=1;

+----+---------+------+

| ID | user_id | name |

+----+---------+------+

| 1 | 0001 | Kate |

| 3 | 1 | Jim |

+----+---------+------+

2 rows in Set (0.00 sec)

Mysql> SELECT * from test_1 where user_id= ' 1 ';

+----+---------+------+

| ID | user_id | name |

+----+---------+------+

| 3 | 1 | Jim |

+----+---------+------+

1 row in Set (0.00 sec)

If the table defines an int field and a string is passed in, the implicit conversion does not occur.

Look at the following tests:

CREATE TABLE ' test_2 ' (
' id ' int (one) not NULL auto_increment,
' user_id ' int (one) is not NULL,
' Name ' varchar (() DEFAULT NULL,
PRIMARY KEY (' id '),
KEY ' idx_user_id ' (' user_id ')
) Engine=innodb auto_increment=4 DEFAULT Charset=utf8

Mysql> Explain select * from Test_2 where user_id=1;
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
| id | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
| 1 | Simple | test_2 | Ref | idx_user_id | idx_user_id | 4 | Const | 2 | |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
1 Row in Set (0.00 sec)

Mysql> Explain select * from test_2 where user_id= ' 1 ';
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
| 1 | Simple | test_2 | Ref | idx_user_id | idx_user_id | 4 | Const | 2 | |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
1 row in Set (0.00 sec)

MySQL SQL optimized string-index implicit conversion