| 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: | |
| 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: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.
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)