MySQL in Operator Not Using Index

  • 13/10/2017
  • 2-minute read

After being alerted for elevated cpu on a staging mysql instance, I logged into the mysql console and ran a SHOW PROCESS LIST :

SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 1
   User: my_user
   Host: x.x.x.x:xxx
     db: my_db
Command: Sleep
   Time: 2
  State:
   Info: NULL
*************************** 2. row ***************************
     Id: 3
   User: my_user
   Host: x.x.x.x:xxx
     db: my_db
Command: Query
   Time: 786
  State: Sending data
   Info: SELECT file_id, fs_file_id
FROM my_data
WHERE file_id IN ('123456', 123457);

A query running for 786 seconds quickly caught my eye, my next course of action was to run an EXPLAIN :


EXPLAIN SELECT file_id, fs_file_id FROM my_data WHERE file_id IN ('123456', 123457)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_data
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 131144420
        Extra: Using where
1 row in set (0.00 sec)

The EXPLAIN confirmed my suspicions, this query was not using an index , and ultimately caused a full table scan .

I was expecting to not find an index for the file_id field, but as you can see this field has an index by being a PRIMARY KEY :


SHOW CREATE TABLE my_data\G
*************************** 1. row ***************************
Table: my_data
Create Table: CREATE TABLE `my_data` (
`file_id` int(10) unsigned NOT NULL,
`fs_file_id` binary(16) DEFAULT NULL,
PRIMARY KEY (`file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

Something I didn’t immediately identify earlier was the mixed types in my IN operator ’s list, they contained both integers and strings :


file_id IN ('123456', 123457)

Once I noticed the mixed type I began debugging, I finally learned the IN operator works great with sets of integers :


EXPLAIN SELECT file_id, fs_file_id FROM my_data WHERE file_id IN (123456, 123457)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_data
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

It even works with sets of strings :


EXPLAIN SELECT file_id, fs_file_id FROM my_data WHERE file_id IN ('123456', '123457')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_data
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

But as we observed from the begining, the IN operator doesn’t act as expected with mixed strings and integers.

DevelopmentMySQL