Slow query _mysql of converting int to varchar in Mysql database

The last week has 2 consecutive processing of slow queries due to the inability to use indexes for int to varchar conversions.

CREATE TABLE ' appstat_day_prototype_201305 ' (' day_key ' date not null DEFAULT ' 1900-01-01 ', ' appkey ' varchar ' is not NULL Default ', ' user_total ' bigint not null default ' 0 ', ' user_activity ' bigint ' is not null default ' 0 ', ' times_total ' bigint () NOT null default ' 0 ', ' times_activity ' bigint ' is not null default ' 0 ', ' incr_login_daily ' bigint is not null Default ' 0 ', ' unbind_total ' bigint not null default ' 0 ', ' unbind_activitys ' bigint is not null default ' 0 ', PRIMARY K  EY (' Appkey ', ' Day_key ')) Engine=innodb DEFAULT Charset=utf8 mysql> explain SELECT * from appstat_day_prototype_201305
where Appkey = xxxxx and day_key between ' 2013-05-23 ' and ' 2013-05-30 '; +----+-------------+------------------------------+------+---------------+------+---------+------+----------+-- -----------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |
Extra | +----+-------------+------------------------------+------+---------------+------+---------+------+----------+-------------+
| 1 | Simple | appstat_day_prototype_201305 | All | PRIMARY | NULL | NULL | NULL | 19285787 |
Using where | +----+-------------+------------------------------+------+---------------+------+---------+------+----------+-- -----------+ 1 row in Set (0.00 sec) mysql> explain SELECT * from appstat_day_prototype_201305 where appkey = ' xxxxx ' A
nd day_key between ' 2013-05-23 ' and ' 2013-05-30 '; +----+-------------+------------------------------+-------+---------------+---------+---------+------+------+-- -----------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |
Extra | +----+-------------+------------------------------+-------+---------------+---------+---------+------+------+-- -----------+
| 1 | Simple | appstat_day_prototype_201305 | Range | PRIMARY | PRIMARY | 65 | NULL | 1 |
Using where | +----+-------------+------------------------------+-------+---------------+---------+---------+------+------+--  -----------+ 1 row in Set (0.00 sec)

It can be seen from above that because appkey is varchar, and in the Where conditions do not add ', will trigger a full table query, add can be used to index, this scan of the number of rows but the difference between the day, the pressure on the server and response time is also the day of the other.

Let's look at another example:

 *************************** 1 row *************************** table:poll_joined_151 Create table:create Table ' poll_joined_151 ' (' poll_id ' bigint (one) not null, ' UID ' bigint (one) not null, ' item_id ' varchar Not null, ' add_time ' int (one) not null default ' 0 ', ' Anonymous ' tinyint (1) NOT null default ' 0 ', ' sub_item ' varchar (12 Not NULL DEFAULT ', key ' idx_poll_id_uid_add_time ' (' poll_id ', ' uid ', ' add_time '), key ' Idx_anonymous_id_addtime ' (' Anonymous ', ' poll_id ', ' add_time ') engine=innodb DEFAULT charset=utf8 SELECT * from poll_joined_151 WHERE poll_id = ' 23489 Anonymous and =0 ORDER by Add_time DESC LIMIT 0, 3 *************************** 1. Row *************************** id:1 select_type:simple table:poll_joined_151 type:ref possible_keys:idx_poll_id_uid _add_time,idx_anonymous_id_addtime key:idx_anonymous_id_addtime key_len:9 ref:const,const rows:30240 extra:using Re 

From the example above, although the poll_id type is bigint, but the SQL added ', but this statement still uses the index, although the number of scanned rows, but can use the index is good SQL.

So a little "why" has so much influence? The root cause is that MySQL makes implicit type conversions when comparing text types to numeric types.

The following is a description of the official 5.5 manual:

If both arguments in a comparison operation are strings, they are compared as strings.
Two parameters are strings, which are compared by string and do not type conversions.
If both arguments are integers, they are, compared as integers.
All two arguments are integers, compared by integers, without type conversions.
Hexadecimal values are treated as binary strings if not compared to a number.
Hexadecimal values are used as binary strings when compared to Non-numeric. If One of the arguments is a TIMESTAMP or DATETIME column and the argument are a constant, the constant is converted To a timestamp before the comparison is performed. This is the done to being more odbc-friendly. The note, that is, does not do for the arguments to in ()! To being safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results if using BETWEEN with date or time values, use CAST () to explicitly convert the Val
UEs to the desired data type. One argument is TIMESTAMP or DATETIME, and the other is a constant, and the constant is converted to TIMESTAMP If one of the arguments is a decimal value, Comparison depends O n the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a Floating-poi
NT value.  One argument is the decimal type, and if another argument is decimal or an integer, the integer is converted to decimal for comparison, and if the other is a floating-point number, the decimal is converted to a floating-point number for comparison in the cases, the  Arguments are compared as floating-point (real) numbers. In all other cases, two parameters are converted to floating-point numbers and then compared

According to the instructions above, when the type of the value after the where condition is inconsistent with the table structure, MySQL does an implicit type conversion and converts it to a floating-point number in the comparison.

For the first case:

such as where string = 1;

You need to convert the strings in the index to floating-point numbers, but because ' 1 ', ' 1 ', ' 1a ' will be converted to 1, so MySQL can not use the index can only be a full table scan, resulting in slow query generation.

mysql> SELECT CAST (' 1 ' as signed) =1;
+-------------------------+
| CAST (' 1 ' as signed) =1 |
+-------------------------+
| 1 |
+-------------------------+
1 row in Set (0.00 sec)
mysql> SELECT CAST (' 1a ' as signed) =1;
+--------------------------+
| CAST (' 1a ' as signed) =1 |
+--------------------------+
| 1 |
+--------------------------+
1 row in set, 1 Warning (0.00 sec)
mysql> SELECT CAST (' 1 ' as signed) =1;
+-----------------------+
| CAST (' 1 ' as signed) =1 |
+-----------------------+
| 1 |
+-----------------------+

At the same time need to pay attention to, because they will be converted to floating-point numbers for comparison, and floating-point number is only 53bit, so when the maximum is exceeded, the comparison will appear problems.

For the second case:

Because the index is based on an int, and the string of pure numbers can be converted into numbers, it can be used to the index, although it will be a certain conversion, consumes a certain amount of resources, but eventually still use the index, will not produce slow query.

Mysql> Select CAST (' as signed ') =
+----------------------------+
| CAST (' as signed) = |
+----------------------------+
| 1 |
+----------------------------+
1 row in Set (0.00 sec)

The above is a small set up to introduce the MySQL database in the varchar of the int transformation caused by slow query, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!