Bug #43319 Table/index scan when quoting large numbers in an 'IN' of a SELECT ... WHERE
Submitted: 3 Mar 2009 2:24 Modified: 29 Jul 2012 23:30
Reporter: Gary Pendergast Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Any
Assigned to: Jørgen Løland CPU Architecture:Any
Tags: Optimizer, performance

[3 Mar 2009 2:24] Gary Pendergast
Description:
When quoting multiple large INTs in the 'IN' of a SELECT ... WHERE, it performs a table or index scan.

How to repeat:
CREATE TABLE a (a INT NOT NULL AUTO_INCREMENT, b INT, PRIMARY KEY(a));
INSERT INTO a(b) VALUES(1),(2),(3),(4),(5);
INSERT INTO a(b) SELECT b FROM a; -- Repeat this line a few times to create data.

Then, compare the output of the following explains:

mysql> EXPLAIN SELECT * FROM a WHERE a IN('9999999999999999999999')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM a WHERE a IN('9999999999999999999999', '9999999999999999999999')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 655360
        Extra: Using where
1 row in set (0.00 sec)
[3 Mar 2009 2:26] Gary Pendergast
Possibly related to Bug #34384
[13 May 2010 18:19] Kolbe Kegel
This seems to happen any time the quoted number is larger than the maximum number supported by the data type of the column being queried:

mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c` char(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=MyISAM AUTO_INCREMENT=32769 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t3 WHERE id IN('4294967296','4294967296')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 32768
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t3 WHERE id IN('4294967295','4294967295')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

mysql> alter table t3 modify id bigint unsigned not null auto_increment;
Query OK, 32768 rows affected (0.81 sec)
Records: 32768  Duplicates: 0  Warnings: 0

mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c` char(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=MyISAM AUTO_INCREMENT=32769 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t3 WHERE id IN('4294967296','4294967296')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT * FROM t3 WHERE id IN('18446744073709551615','18446744073709551615')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t3 WHERE id IN('18446744073709551616','18446744073709551616')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 32768
        Extra: Using where
1 row in set (0.00 sec)

This does seem to be a bug of some sort, but it should only manifest itself in some situation where a user is trying to match a value that could not possibly appear in the column being queried.
[29 Jul 2012 23:30] Paul DuBois
Noted in 5.6.6 changelog.

The optimizer could chose a worse execution plan for a condition that
used a quoted number compared to the unquoted number.