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:
None 
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:19] monty solomon
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
[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