| Bug #83856 | Index ref not used for multi-column IN - type or collation conversion warning | ||
|---|---|---|---|
| Submitted: | 17 Nov 2016 4:19 | Modified: | 18 Dec 2016 21:47 |
| Reporter: | monty solomon | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7.13,5.7.16 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[17 Nov 2016 4:20]
monty solomon
Run EXPLAIN on a query that uses the single-column IN clause and observe that there is no warning about type or collation conversion warning
mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (alternateId) IN ('user@oracle.com')\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: NULL
1 row in set, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `avatars`.`enrichmentavatars`.`objectType` AS `objectType`,`avatars`.`enrichmentavatars`.`alternateId` AS `alternateId`,`avatars`.`enrichmentavatars`.`fileManagerKey` AS `fileManagerKey`,`avatars`.`enrichmentavatars`.`timestamp` AS `timestamp` from `avatars`.`enrichmentavatars` where (`avatars`.`enrichmentavatars`.`alternateId` = 'user@oracle.com')
1 row in set (0.00 sec)
[17 Nov 2016 4:22]
monty solomon
Run EXPLAIN on a query that does not use an IN clause and observe that there is no warning about type or collation conversion
mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE objectType=1 AND alternateId='user@oracle.com'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: enrichmentAvatars
partitions: NULL
type: const
possible_keys: alternateId_objectType
key: alternateId_objectType
key_len: 1023
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select '1' AS `objectType`,'user@oracle.com' AS `alternateId`,NULL AS `fileManagerKey`,'1476923413134' AS `timestamp` from `avatars`.`enrichmentavatars` where 1
1 row in set (0.00 sec)
[17 Nov 2016 4:31]
monty solomon
The warnings are missing when the query is run without the EXPLAIN
mysql> SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (objectType, alternateId) IN ((1,'user@oracle.com'),(2,'user2@oracle.com'))\G
*************************** 1. row ***************************
objectType: 1
alternateId: user@oracle.com
fileManagerKey: NULL
timestamp: 1476923413134
*************************** 2. row ***************************
objectType: 2
alternateId: user2@oracle.com
fileManagerKey: NULL
timestamp: 1476823413134
2 rows in set (0.00 sec)
mysql> show warnings\G
Empty set (0.00 sec)
[17 Nov 2016 20:29]
monty solomon
Updated synopsis. Verified on version 5.7.16.
[23 Nov 2016 11:14]
Manyi Lu
Could you please check the following setting: character_set_client character_set_connection collation_connection See also http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html.
[23 Nov 2016 11:32]
Manyi Lu
Beware that the default collation for utf8mb4 is utf8mb4_general_ci, and not utf8mb4_bin. So setting character_set_connection would implicitly set collation_connection to the default if no collation is explicitly set, in this case to utf8mb4_general_ci. The column alternateID is defined with a different collation, utf8mb4_bin. alternateId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin That is likely the reason for Message: Cannot use ref access on index 'alternateId_objectType' due to type or collation conversion on field 'alternateId'
[13 Dec 2016 4:38]
monty solomon
I changed to utf8 without the bin collation and it exhibits the same behavior. It appears to be an issue with the processing of IN().
mysql> status
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
mysql> select @@character_set_client;
+------------------------+
| @@character_set_client |
+------------------------+
| utf8 |
+------------------------+
1 row in set (0.00 sec)
mysql> select @@character_set_connection;
+----------------------------+
| @@character_set_connection |
+----------------------------+
| utf8 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select @@collation_connection;
+------------------------+
| @@collation_connection |
+------------------------+
| utf8_general_ci |
+------------------------+
1 row in set (0.00 sec)
CREATE TABLE `enrichmentAvatars` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`objectType` tinyint(3) unsigned NOT NULL,
`alternateId` varchar(255) CHARACTER SET utf8 NOT NULL,
`fileManagerKey` varchar(255) DEFAULT NULL,
`timestamp` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `alternateId_objectType` (`alternateId`,`objectType`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
mysql> INSERT INTO enrichmentAvatars SET objectType=1, alternateId='user@oracle.com', timestamp = 1476923413134;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user2@oracle.com', timestamp = 1476823413134;
Query OK, 1 row affected (0.00 sec)
mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (objectType, alternateId) IN ((1,'user@oracle.com'),(2,'user2@oracle.com'))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: enrichmentAvatars
partitions: NULL
type: ALL
possible_keys: alternateId_objectType
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
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 ref access on index 'alternateId_objectType' due to type or collation conversion on field 'alternateId'
*************************** 3. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `Avatars`.`enrichmentAvatars`.`objectType` AS `objectType`,`Avatars`.`enrichmentAvatars`.`alternateId` AS `alternateId`,`Avatars`.`enrichmentAvatars`.`fileManagerKey` AS `fileManagerKey`,`Avatars`.`enrichmentAvatars`.`timestamp` AS `timestamp` from `Avatars`.`enrichmentAvatars` where ((`Avatars`.`enrichmentAvatars`.`objectType`,`Avatars`.`enrichmentAvatars`.`alternateId`) in (<cache>((1,'user@oracle.com')),<cache>((2,'user2@oracle.com'))))
3 rows in set (0.00 sec)
[18 Dec 2016 20:46]
MySQL Verification Team
5.6 - verified
mysql> show global variables like 'chara%';
+--------------------------+---------------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-cluster-gpl-7.4.11-linux-glibc2.5-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> show variables like 'chara%';
+--------------------------+---------------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-cluster-gpl-7.4.11-linux-glibc2.5-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> CREATE DATABASE Avatars DEFAULT CHARACTER SET utf8mb4; USE Avatars;
Query OK, 1 row affected (0.03 sec)
Database changed
mysql> STATUS
--------------
bin/mysql Ver 14.14 Distrib 5.6.29-ndb-7.4.11, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 15
Current database: Avatars
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.29-ndb-7.4.11-cluster-gpl MySQL Cluster Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql.sock
Uptime: 36 days 30 min 19 sec
Threads: 2 Questions: 1500674 Slow queries: 0 Opens: 92 Flush tables: 2 Open tables: 12 Queries per second avg: 0.482
--------------
mysql> 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 DEFAULT CHARSET=ascii ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql> CREATE TABLE `enrichmentAvatars` (
-> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
-> `objectType` tinyint(3) unsigned NOT NULL,
-> `alternateId` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
-> `fileManagerKey` varchar(100) DEFAULT NULL,
-> `timestamp` bigint(20) unsigned NOT NULL,
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `alternateId_objectType` (`alternateId`,`objectType`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected, 4 warnings (0.16 sec)
mysql> show warnings
-> ;
+---------+------+-----------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------+
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. |
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8. |
| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
+---------+------+-----------------------------------------------------------------------+
4 rows in set (0.00 sec)
[18 Dec 2016 20:46]
MySQL Verification Team
mysql> INSERT INTO enrichmentAvatars SET objectType=1, alternateId='user@oracle.com', timestamp = 1476923413134;
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user2@oracle.com', timestamp = 1476823413134;
Query OK, 1 row affected (0.00 sec)
mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (objectType, alternateId) IN ((1,'user@oracle.com'),(2,'user2@oracle.com'))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: enrichmentAvatars
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
1 row in set (0.03 sec)
mysql> show warnings;
Empty set (0.00 sec)
mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (alternateId) IN ('user@oracle.com')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: enrichmentAvatars
type: ref
possible_keys: alternateId_objectType
key: alternateId_objectType
key_len: 402
ref: const
rows: 1
Extra: Using index condition
1 row in set (0.03 sec)
mysql> show warnings;
Empty set (0.00 sec)
mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user3@oracle.com', timestamp = 1476823413134; Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user4@oracle.com', timestamp = 1476823413134;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user5@oracle.com', timestamp = 1476823413134;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user6@oracle.com', timestamp = 1476823413134;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user7@oracle.com', timestamp = 1476823413134;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user8@oracle.com', timestamp = 1476823413134;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user9@oracle.com', timestamp = 1476823413134;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user10@oracle.com', timestamp = 1476823413134;
Query OK, 1 row affected (0.00 sec)
mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (objectType, alternateId) IN ((1,'user@oracle.com'),(2,'user2@oracle.com'))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: enrichmentAvatars
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where
1 row in set (0.00 sec)
mysql>
[18 Dec 2016 21:47]
MySQL Verification Team
5.7.17 - verified
mysql> show global variables like 'chara%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> show variables like 'chara%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> select @@version
-> ;
+-----------+
| @@version |
+-----------+
| 5.7.17 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE DATABASE Avatars DEFAULT CHARACTER SET utf8mb4; USE Avatars;
Query OK, 1 row affected (0.00 sec)
Database changed
mysql> STATUS
--------------
mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper
Connection id: 4
Current database: Avatars
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.17 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 8 min 27 sec
Threads: 1 Questions: 25 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 100 Queries per second avg: 0.049
--------------
[18 Dec 2016 21:48]
MySQL Verification Team
mysql> 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 DEFAULT CHARSET=ascii ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.31 sec)
mysql> INSERT INTO enrichmentAvatars SET objectType=1, alternateId='user@oracle.com', timestamp = 1476923413134;
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user2@oracle.com', timestamp = 1476823413134;
Query OK, 1 row affected (0.00 sec)
mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (objectType, alternateId) IN ((1,'user@oracle.com'),(2,'user2@oracle.com'))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: enrichmentAvatars
partitions: NULL
type: ALL
possible_keys: alternateId_objectType
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
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 ref access on index 'alternateId_objectType' due to type or collation conversion on field 'alternateId'
*************************** 3. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `Avatars`.`enrichmentAvatars`.`objectType` AS `objectType`,`Avatars`.`enrichmentAvatars`.`alternateId` AS `alternateId`,`Avatars`.`enrichmentAvatars`.`fileManagerKey` AS `fileManagerKey`,`Avatars`.`enrichmentAvatars`.`timestamp` AS `timestamp` from `Avatars`.`enrichmentAvatars` where ((`Avatars`.`enrichmentAvatars`.`objectType`,`Avatars`.`enrichmentAvatars`.`alternateId`) in (<cache>((1,'user@oracle.com')),<cache>((2,'user2@oracle.com'))))
3 rows in set (0.00 sec)
mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (alternateId) IN ('user@oracle.com')\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: NULL
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE objectType=1 AND alternateId='user@oracle.com'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: enrichmentAvatars
partitions: NULL
type: const
possible_keys: alternateId_objectType
key: alternateId_objectType
key_len: 1023
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select '1' AS `objectType`,'user@oracle.com' AS `alternateId`,NULL AS `fileManagerKey`,'1476923413134' AS `timestamp` from `Avatars`.`enrichmentAvatars` where 1
1 row in set (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select '1' AS `objectType`,'user@oracle.com' AS `alternateId`,NULL AS `fileManagerKey`,'1476923413134' AS `timestamp` from `Avatars`.`enrichmentAvatars` where 1
1 row in set (0.00 sec)
mysql> SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (objectType, alternateId) IN ((1,'user@oracle.com'),(2,'user2@oracle.com'))\G
*************************** 1. row ***************************
objectType: 1
alternateId: user@oracle.com
fileManagerKey: NULL
timestamp: 1476923413134
*************************** 2. row ***************************
objectType: 2
alternateId: user2@oracle.com
fileManagerKey: NULL
timestamp: 1476823413134
2 rows in set (0.00 sec)
mysql> show warnings\G
Empty set (0.00 sec)
[18 Dec 2016 21:49]
MySQL Verification Team
Hi, Verified as stated. Severity dropped to S3 as this is hardly S1. Thanks for your submission kind regards Bogdan Kecman
[15 Nov 2019 17:31]
Fero Us
This does not have to be a multi-column. It is also a repro with a single column index
Description: We observed some unexpected performance issues and discovered that multi-column IN clause was not using the index due to a type or collation conversion. Cannot use ref access on index 'alternateId_objectType' due to type or collation conversion on field 'alternateId' How to repeat: Configure server to use utf8mb4 in /etc/my.cnf default-character-set = utf8mb4 character-set-server = utf8mb4 Create and use a new database mysql> CREATE DATABASE Avatars DEFAULT CHARACTER SET utf8mb4; USE Avatars; Query OK, 1 row affected (0.00 sec) Confirm the settings mysql> STATUS Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 Create a new table mysql> 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 DEFAULT CHARSET=ascii ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; Query OK, 0 rows affected (0.02 sec) Populate the table mysql> INSERT INTO enrichmentAvatars SET objectType=1, alternateId='user@oracle.com', timestamp = 1476923413134; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO enrichmentAvatars SET objectType=2, alternateId='user2@oracle.com', timestamp = 1476823413134; Query OK, 1 row affected (0.00 sec) Run EXPLAIN on a query that uses the multi-column IN clause mysql> EXPLAIN SELECT objectType, alternateId, fileManagerKey, timestamp FROM enrichmentAvatars WHERE (objectType, alternateId) IN ((1,'user@oracle.com'),(2,'user2@oracle.com'))\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: enrichmentAvatars partitions: NULL type: ALL possible_keys: alternateId_objectType key: NULL key_len: NULL ref: NULL rows: 2 filtered: 100.00 Extra: Using where 1 row in set, 3 warnings (0.00 sec) Observe that ref is NULL and view the warnings 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 ref access on index 'alternateId_objectType' due to type or collation conversion on field 'alternateId' *************************** 3. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `avatars`.`enrichmentavatars`.`objectType` AS `objectType`,`avatars`.`enrichmentavatars`.`alternateId` AS `alternateId`,`avatars`.`enrichmentavatars`.`fileManagerKey` AS `fileManagerKey`,`avatars`.`enrichmentavatars`.`timestamp` AS `timestamp` from `avatars`.`enrichmentavatars` where ((`avatars`.`enrichmentavatars`.`objectType`,`avatars`.`enrichmentavatars`.`alternateId`) in (<cache>((1,'user@oracle.com')),<cache>((2,'user2@oracle.com')))) 3 rows in set (0.00 sec) Suggested fix: Check and fix code path for multi-column IN clause