MySQL implicit type conversion causes index invalidation

A problem was found today. The column of the where condition clearly has an index, but the execution plan is still a full table scan.

Mysql > explain select task_id FROM mostop_xiaodai_collection_call_auto WHERE task_id = 433423882127424 AND task_data_id = 27739131157286912 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+- -+ | 1 | SIMPLE | mostop_xiaodai_collection_call_auto | NULL | ALL | IDX_task_id IDX_task_data_id | NULL | 3101134 | 1.00 | Using where | +-+ -+ 1 row in set 5 warnings (0.00 sec)

View table structure

Mysql > show create table mostop_xiaodai_collection_call_auto\ gateway * 1. Row * * Table: mostop_xiaodai_collection_call_autoCreate Table: CREATE TABLE `mostop_xiaodai_collection_call_ auto` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT COMMENT'No. `agentid` int (10) unsigned NOT NULL COMMENT 'channel ID', `loan_ id` bigint (20) unsigned NOT NULL DEFAULT' 0' COMMENT 'loan ID', `call_ id`bigint (20) unsigned NOT NULL COMMENT' borrower ID', `call_ mobile` varchar (20) NOT NULL COMMENT 'cell phone number', `call_ name`varchar (20) NOT NULL COMMENT 'name', `call_ sex`varchar (20) NOT NULL COMMENT 'gender', `call_due_ date`date NOT NULL COMMENT 'due date' `Day`int (10) NOT NULL COMMENT 'overdue days (negative is the number of days before the loan), `call_talking_ type' varchar (50) NOT NULL COMMENT 'speech (A: reminder 7 days before the loan B: 3 days before the loan C: 3 days before the loan D: 1 day before the loan Ps: if the call is empty The rest such as due_one have been abandoned), `third_party_ name`varchar (3) NOT NULL COMMENT 'platform name', `third_party_ system` tinyint (3) unsigned NOT NULL COMMENT 'third-party IVR system, 1 = 100 recordable (suspended), 2 = Datang, 3 = Yundian gang (suspended), 4 = iFLYTEK, 5 = Ji Lixun 99 = decision engine test', 'send_ time` datetime DEFAULT NULL COMMENT' send time', `send_ status` tinyint (3) unsigned NOT NULL DEFAULT'0' COMMENT 'send status, 0 = not sent, 1 = sent successfully, 2 = sent failed', `send_ result`tinyint (3) DEFAULT'0' COMMENT 'IVR result (0 = invalid answer, or not answered, 1 = valid answer Users have been notified)', 'talking_ time` int (10) NOT NULL DEFAULT' 0' COMMENT 'talk duration', 'task_ id` varchar (32) NOT NULL DEFAULT' 0' COMMENT 'outbound task ID (for iFLYTEK service only)', `task_data_ id` varchar (32) NOT NULL DEFAULT'0' COMMENT 'outbound task secondary ID (for iFLYTEK service only)', `resend_ num`tinyint (3) unsigned NOT NULL DEFAULT'0' COMMENT'' `updated_ time` datetime DEFAULT NULL COMMENT 'receive time', `receive_ content` text COMMENT 'receive content', `unique_ Tab` varchar (100) DEFAULT NULL COMMENT 'unique ID', `created_ at` int (10) unsigned NOT NULL DEFAULT'0' COMMENT 'creation time', `updated_ at` int (10) unsigned NOT NULL DEFAULT'0' COMMENT 'update time', PRIMARY KEY (`id`), KEY `loan_ id` (`agentid`, `loan_ id`), KEY `send_ result` (`call_ mobile`, `send_ time`) KEY `receive_ time` (`receive_ time`), KEY `IDX_task_ id` (`task_ id`), KEY `IDX_task_data_ id` (`task_data_ id`) ENGINE=InnoDB AUTO_INCREMENT=5292233 DEFAULT CHARSET=utf8 COMMENT=' to collect automatic call list'1 row in set (0.00 sec)

View warnings

Because these two are listed as string types, but there are no quotation marks in SQL!

`task_data_ id` varchar (32) `task_data_ id` varchar (32)

Check the execution plan after the quotation marks

Mysql > explain select task_id FROM mostop_xiaodai_collection_call_auto WHERE task_id = '433423882127424' AND task_data_id =' 27739131157286912' +-+- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+-+-- -+-+ | 1 | SIMPLE | mostop_xiaodai_collection_call_auto | NULL | ref | IDX_task_id IDX_task_data_id | IDX_task_data_id | 98 | const | 1 | 5.00 | Using where | +-+ -+ 1 row in set 1 warning (0.00 sec)