MySQL slow query-implicit conversion of inconsistent value type and field type

Sample slow SQL

SELECT * FROM task_engine_base_data WHERE outer_task_id = 1001871

Scene context

• Data table structure status

In order to reduce reading interference, fields and index definitions that are irrelevant to this case have been removed

CREATE TABLE `task_engine_base_data` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `task_type_id` int(11) NOT NULL DEFAULT '0' COMMENT '任务类型ID',
  `outer_task_id` varchar(64) NOT NULL DEFAULT '' COMMENT '外部任务ID',
  PRIMARY KEY (`id`),
  KEY `idx_outer_id_type_id` (`outer_task_id`,`task_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='子任务规则表';

Cause Analysis

EXPLAIN for slow SQL

idx_outer_id_type_id(outer_task_id,task_type_id) exists in the table.

The field definition is outer_task_id varchar(64) NOT NULL DEFAULT '' .

The field type is a string, but the value of the query condition is an integer, which leads to an implicit conversion by MySQL.

The column value will be converted to a floating-point number type, that is, CAST(col_name AS DOUBLE) = XXX . The column value is calculated, and cannot use the index .

solution

The type of the forced conversion value in the program is completely consistent with the database field type to avoid implicit conversion.

SELECT * FROM task_engine_base_data WHERE outer_task_id = '1001871'

Or use CAST convert the query value:

SELECT * FROM task_engine_base_data WHERE outer_task_id=CAST(1001871 AS CHAR)

Optimized SQL

SELECT * FROM task_engine_base_data WHERE outer_task_id = '1001871'

Look at the optimized EXPLAIN

Knowledge summary

When the basic types of two values to be compared are different, conversion occurs.

In the comparison of integers and strings, both values will be converted into floating-point numbers and then compared.

Performing calculations on indexed columns will result in the inability to use the index.

Reference

• 《MySQL 5.7 Reference Manual》Type Conversion in Expression Evaluation

---

Thank you for reading, think the content is good, please like it 😆
Original address: https://shockerli.net/post/mysql-slow-sql-implicit-type-conversion/