| Bug #83857 | Index not used for the implicit integer to string conversion | ||
|---|---|---|---|
| Submitted: | 17 Nov 2016 5:18 | Modified: | 21 Nov 2016 19:48 |
| Reporter: | monty solomon | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.7.13, 5.7.16 | OS: | CentOS |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[21 Nov 2016 12:24]
MySQL Verification Team
Hi, This is not a bug but designed behavior. We are not supporting indexes for functions irrelevant if the functional conversion is explicit or implicit. take care Bogdan Kecman
[21 Nov 2016 17:32]
monty solomon
The column is not being used in a function. The value compared with the column is a being converted and is a constant after the conversion.
The issue I reported is that when an integer is specified as a comparison for a string column it is not being converted to a string for comparison with the index.
In the following examples, using explicit or implicit conversion, the index is being used because the value 1234567 is converted to the appropriate constant.
mysql> explain select count(1) from enrichmentavatars where alternateId=convert(1234567, char character set utf8mb4)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: enrichmentavatars
partitions: NULL
type: ref
possible_keys: alternateId_objectType
key: alternateId_objectType
key_len: 1022
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(1) from enrichmentavatars where alternateId=convert(1234567 using utf8mb4)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: enrichmentavatars
partitions: NULL
type: ref
possible_keys: alternateId_objectType
key: alternateId_objectType
key_len: 1022
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(1) from enrichmentavatars where alternateId='1234567'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: enrichmentavatars
partitions: NULL
type: ref
possible_keys: alternateId_objectType
key: alternateId_objectType
key_len: 1022
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
[21 Nov 2016 17:41]
monty solomon
Section 13.2 of the manual (http://dev.mysql.com/doc/refman/5.7/en/type-conversion.html ) states For example, MySQL automatically converts numbers to strings as necessary, and vice versa.
[21 Nov 2016 18:05]
MySQL Verification Team
Behavior documented in our documentation http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html Comparison of dissimilar columns (comparing a string column to a temporal or numeric column, for example) may prevent use of indexes if values cannot be compared directly without conversion. For a given value such as 1 in the numeric column, it might compare equal to any number of values in the string column such as '1', ' 1', '00001', or '01.e1'. This rules out use of any indexes for the string column. as well as in many other places in past many years for e.g. https://makandracards.com/makandra/14955-mysql-will-not-use-indexes-if-you-query-the-wrong...
[21 Nov 2016 19:48]
monty solomon
Thanks for the feedback and clarification.
Description: When using the implicit conversion between integer and string the index is not used. When using the explicit conversion between integer and string the index is used. Why isn't the index used in the implicit case? The index is used for the implicit string to integer conversion. How to repeat: mysql> show create table enrichmentavatars\G *************************** 1. row *************************** Table: enrichmentavatars Create Table: CREATE TABLE `enrichmentavatars` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `objectType` tinyint(3) unsigned NOT NULL, `alternateId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `fileManagerKey` varchar(255) DEFAULT NULL, `timestamp` bigint(20) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `alternateId_objectType` (`alternateId`,`objectType`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=ascii ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 1 row in set (0.00 sec) mysql> select * from enrichmentavatars\G *************************** 1. row *************************** id: 1 objectType: 1 alternateId: user@oracle.com fileManagerKey: NULL timestamp: 1476923413134 *************************** 2. row *************************** id: 2 objectType: 2 alternateId: user2@oracle.com fileManagerKey: NULL timestamp: 1476823413134 *************************** 3. row *************************** id: 3 objectType: 3 alternateId: 1234567 fileManagerKey: NULL timestamp: 1476723413134 3 rows in set (0.00 sec) mysql> explain select count(1) from enrichmentavatars where alternateId=1234567\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentavatars partitions: NULL type: index possible_keys: alternateId_objectType key: alternateId_objectType key_len: 1023 ref: NULL rows: 3 filtered: 33.33 Extra: Using where; Using index 1 row in set, 3 warnings (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1739 Message: Cannot use ref access on index 'alternateId_objectType' due to type or collation conversion on field 'alternateId' *************************** 2. row *************************** Level: Warning Code: 1739 Message: Cannot use range access on index 'alternateId_objectType' due to type or collation conversion on field 'alternateId' *************************** 3. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select count(1) AS `count(1)` from `avatars`.`enrichmentavatars` where (`avatars`.`enrichmentavatars`.`alternateId` = 1234567) 3 rows in set (0.00 sec) mysql> explain select count(1) from enrichmentavatars where alternateId=convert(1234567, char character set utf8mb4)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentavatars partitions: NULL type: ref possible_keys: alternateId_objectType key: alternateId_objectType key_len: 1022 ref: const rows: 1 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select count(1) AS `count(1)` from `avatars`.`enrichmentavatars` where (`avatars`.`enrichmentavatars`.`alternateId` = cast(1234567 as char charset utf8mb4)) 1 row in set (0.00 sec) mysql> explain select count(1) from enrichmentavatars where alternateId=convert(1234567 using utf8mb4)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentavatars partitions: NULL type: ref possible_keys: alternateId_objectType key: alternateId_objectType key_len: 1022 ref: const rows: 1 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select count(1) AS `count(1)` from `avatars`.`enrichmentavatars` where (`avatars`.`enrichmentavatars`.`alternateId` = convert(1234567 using utf8mb4)) 1 row in set (0.00 sec) mysql> explain select count(1) from enrichmentavatars where alternateId='1234567'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentavatars partitions: NULL type: ref possible_keys: alternateId_objectType key: alternateId_objectType key_len: 1022 ref: const rows: 1 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select count(1) AS `count(1)` from `avatars`.`enrichmentavatars` where (`avatars`.`enrichmentavatars`.`alternateId` = '1234567') 1 row in set (0.00 sec) The index is used for the implicit string to integer conversion. mysql> alter table enrichmentavatars add index(objectType); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select count(1) from enrichmentavatars where objectType='1'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentavatars partitions: NULL type: ref possible_keys: objectType key: objectType key_len: 1 ref: const rows: 1 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select count(1) AS `count(1)` from `avatars`.`enrichmentavatars` where (`avatars`.`enrichmentavatars`.`objectType` = '1') 1 row in set (0.00 sec) mysql> explain select count(1) from enrichmentavatars where objectType=1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentavatars partitions: NULL type: ref possible_keys: objectType key: objectType key_len: 1 ref: const rows: 1 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select count(1) AS `count(1)` from `avatars`.`enrichmentavatars` where (`avatars`.`enrichmentavatars`.`objectType` = 1) 1 row in set (0.00 sec) Suggested fix: Use the index with the implicit integer to string conversion similar to how it works for the implicit string to integer conversion.