Under what circumstances does a varchar int query index?

In fact, many developers who have worked for several years sometimes feel dizzy. Here is a test with specific examples.

1. Preparations

Prepare two tables for subsequent tests.

Table 1: Create table test1 with 3 columns, where id is the primary key (int), c_no is int, has index, and c_2 is a common field

/*Create table test1 */
create table  test1(id int primary key,c_no  int ,c_2 varchar(1),key c_no(c_no));

/* Insert some test data */
insert  into test1 values(1,1,'0'),(2,2,'1'),(3,4,'1'),(4,6,'0'),(5,7,''1),(6,11,'2'),(7,5,'3'),(8,100,'0'),(9,30,'1'),(10,50,'0');

Table 2: Create table test1 with 3 columns, where id is the primary key (int), c_no is the character type, and has index, c_2 is the common field

/* Create test2 */
create table  test2(id int primary key  auto_increment,c_no  varchar(11) ,c2 varchar(2),key c_no(c_no));

/* Insert some test data */
 insert  into test2 values(1,'5','1'),(4,'100','0'),(3,'30','1'),(10,'500','0'),(11,'20','0'),(12,'20a','0'),(15,'020b','1');

The difference between the two tables is that the field types of c_no are different.

 

2. Equivalence Query Test

2.1 Testing test1

The test1.c_no field is of type int, which is compared below with integers and strings to see if they are indexed.The corresponding implementation plans are as follows:

mysql> explain  select *  from test1 where c_no='100';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test1 | NULL       | ref  | c_no          | c_no | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain  select *  from test1 where c_no=100;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test1 | NULL       | ref  | c_no          | c_no | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

You can see that both ways are indexed, and c_no is indexed, ref is const (constant equivalent query), and the number of swept rows is 1

That is, when the field type in the table is integer, the query can be indexed with either string-type or int-type numbers.It is easy to understand that the value query of type int can walk the index, so why can the character type walk?In fact, the character type here has been implicitly transformed, which in the above example is equivalent to

mysql> explain  select *  from test1 where c_no=CAST('100' as UNSIGNED);
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test1 | NULL       | ref  | c_no          | c_no | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

 

2.2 Testing the test2 table

Test the query for test2 in the same way

First test the normal character type and character type comparison, the results can be imagined, you can walk the index, as follows:

mysql> explain  select *  from test2 where c_no='100';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test2 | NULL       | ref  | c_no          | c_no | 47      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

And what if it's an integer?The results are as follows (unfortunately, you can't index anymore)

mysql> explain  select *  from test2 where c_no=100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | ALL  | c_no          | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

That is, when a field in a table is of character type, the query cannot walk an index when the value of the query is integer.

That corresponds to the following:

mysql> explain  select *  from test2 where cast(c_no  as  unsigned)=100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

That is, c_no is implicitly transformed.Because if 100 makes a movie conversion, the result should be indexed, for example:

mysql> explain  select *  from test2 where c_no=cast(100 as char);
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test2 | NULL       | ref  | c_no          | c_no | 47      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Thus, we also demonstrate that if a field has been computed as a function, it cannot be used even with an index on that column (prior to MySQL 8.0).

2.3 Further testing

In fact, one more thing you can test for the test2 table is that the c_no field has been implicitly transformed, for example:

mysql> select  * from test2 where c_no=20;
+----+------+------+
| id | c_no | c2   |
+----+------+------+
| 11 | 20   | 0    |
| 12 | 20a  | 0    |
| 15 | 020b | 1    |
+----+------+------+
3 rows in set, 2 warnings (0.00 sec)

In addition, you saw two warnings as follows:

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '20a'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: '020b' |
+---------+------+------------------------------------------+
2 rows in set (0.00 sec)

More proof of conversion to number (pre-conversion to double)

 

3. Summary

From the simple test above, the following conclusions can be found:

  • When the field type in the table is integer, it can be indexed whether the query uses a string number or an int number.
  • When the field in the table is of character type, the index cannot be used when the value of the query is integer type.
  • If a field has been calculated as a function, it will not be available even if it has an index on it (versions prior to MySQL 8.0)

Therefore, the development classmates should pay attention to the writing of SQL when writing SQL. The lack of a single quotation mark may lead to a large performance difference.

Tags: MySQL SQL

Posted on Fri, 03 Apr 2020 02:02:54 -0700 by JesperBisgaard