MySQL Bugs: #70782: Query slow down and server overload when type parameter is wrong.

Bug #70782	Query slow down and server overload when type parameter is wrong.
Submitted:	31 Oct 2013 9:52	Modified:	6 Jul 2014 13:37
Reporter:	Alessio Palma	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S5 (Performance)
Version:	5.6.10-enterprise-commercial-advanced-lo	OS:	Linux (2.6.32-358.6.1.el6.x86_6)
Assigned to:		CPU Architecture:	Any
Tags:	Query implicit conversion full table scan

Description:
explain select count(1) from att_transaction where ACTIVITY_ID = 'Dh4x9uSrF584LBiFlC8AgV3JOZNqtKbJUEGO';
+----+-------------+-----------------+-------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table           | type  | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-----------------+-------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | att_transaction | const | ACTIVITY_ID   | ACTIVITY_ID | 121     | const |    1 | Using index |
+----+-------------+-----------------+-------+---------------+-------------+---------+-------+------+-------------+

searching an integer value instead a string, optimizer stop using the index and do full table scan ( WHY? )

explain select count(1) from att_transaction where ACTIVITY_ID = 9769718533;
+----+-------------+-----------------+-------+---------------+-------------+---------+------+---------+--------------------------+
| id | select_type | table           | type  | possible_keys | key         | key_len | ref  | rows    | Extra                    |
+----+-------------+-----------------+-------+---------------+-------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | att_transaction | index | ACTIVITY_ID   | ACTIVITY_ID | 121     | NULL | 3442831 | Using where; Using index |
+----+-------------+-----------------+-------+---------------+-------------+---------+------+---------+--------------------------+

How to repeat:
I found the problem on this table definition

CREATE TABLE `att_transaction` (
  `FINANCIAL_EVENT_ID` bigint(8) NOT NULL,
  `POST_DATE` datetime NOT NULL,
  `ACTIVITY_ID` char(40) DEFAULT NULL,
  `ACTIVITY_DATE` datetime DEFAULT NULL,
  `ACTIVITY_TYPE` varchar(20) DEFAULT NULL,
  `PARENT_FINANCIAL_EVENT_ID` bigint(8) NOT NULL,
  `PARENT_ACTIVITY_ID` char(40) DEFAULT NULL,
  `PARENT_ACTIVITY_DATE` datetime DEFAULT NULL,
  `REGISTRATION_TIME` datetime DEFAULT NULL,
  `ACCOUNT_ID` char(40) DEFAULT NULL,
  `NO_ID_FOR_ACCOUNT` char(40) DEFAULT NULL,
  `PHONE_NUM` char(14) DEFAULT NULL,
  `REFUND_DISPUTE_STATUS` varchar(20) DEFAULT NULL,
  `REFUND_REQUEST_REASON` varchar(100) DEFAULT NULL,
  `REFUND_STATUS_COMMENT` varchar(100) DEFAULT NULL,
  `ANTICIPATED_SETTLEMENT_DATE` datetime DEFAULT NULL,
  `INSTANCE_ID` varchar(20) DEFAULT NULL,
  `CAMPAIGN_ID` bigint(8) DEFAULT NULL,
  `LICENSE_EXPIRE_DATE` datetime DEFAULT NULL,
  `DEVICE_MAKE` varchar(20) DEFAULT NULL,
  `DEVICE_MODEL` varchar(20) DEFAULT NULL,
  `REFUND_TYPE` char(1) DEFAULT NULL,
  `PROCESSED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`FINANCIAL_EVENT_ID`),
  UNIQUE KEY `ACTIVITY_ID` (`ACTIVITY_ID`),
  KEY `PHONE_NUM` (`PROCESSED`,`PHONE_NUM`),
  KEY `ACTIVITY_TYPE` (`ACTIVITY_TYPE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

explain select count(1) from att_transaction where ACTIVITY_ID = 'Dh4x9uSrF584LBiFlC8AgV3JOZNqtKbJUEGO';

explain select count(1) from att_transaction where ACTIVITY_ID = 9769718533;

Did you see the warnings?

mysql> explain extended select count(1) from att_transaction where ACTIVITY_ID = 9769718533;
+----+-------------+-----------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table           | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | att_transaction | index | ACTIVITY_ID   | ACTIVITY_ID | 121     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-----------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'ACTIVITY_ID' due to type or collation conversion on field 'ACTIVITY_ID'                                |
| Warning | 1739 | Cannot use range access on index 'ACTIVITY_ID' due to type or collation conversion on field 'ACTIVITY_ID'                              |
| Note    | 1003 | /* select#1 */ select count(1) AS `count(1)` from `test`.`att_transaction` where (`test`.`att_transaction`.`ACTIVITY_ID` = 9769718533) |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

No, I did not see.
I got it, however as long as 

12.2. Type Conversion in Expression Evaluation
When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa.

I still wonder why mysqld is not able to use the index.
If you just add "'" query runs faster.

mysql> select count(1) from att_transaction where ACTIVITY_ID = 1;

... too long ...

^CCtrl-C -- sending "KILL QUERY 4080150" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

mysql> select count(1) from att_transaction where ACTIVITY_ID = '1';
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Should not be performed implicitly ?

Please check 5.6.19. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".