| Bug #66032 | Full table scan when using IN with INT values greater than 2^32 | ||
|---|---|---|---|
| Submitted: | 26 Jul 2012 17:38 | Modified: | 27 Jul 2012 18:33 |
| Reporter: | Gavin Towey | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.1.64, 5.5.13 | OS: | Any (osx/linux) |
| Assigned to: | CPU Architecture: | Any | |
[27 Jul 2012 4:17]
Valeriy Kravchuk
Thank you for the problem report. Verified with 5.1.64 also:
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.64-debug Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table scantest ( a int unsigned not null auto_increment primary key) ;
Query OK, 0 rows affected (0.16 sec)
mysql> insert into scantest (a) values (1), (2), (3);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into scantest (a) select NULL from scantest a, scantest b;
Query OK, 9 rows affected (0.09 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> insert into scantest (a) select NULL from scantest a, scantest b;
Query OK, 144 rows affected (0.01 sec)
Records: 144 Duplicates: 0 Warnings: 0
mysql> insert into scantest (a) select NULL from scantest a, scantest b;
Query OK, 24336 rows affected (0.38 sec)
Records: 24336 Duplicates: 0 Warnings: 0
mysql> select max(a) from scantest;
+--------+
| max(a) |
+--------+
| 24492 |
+--------+
1 row in set (0.05 sec)
mysql> explain extended select * from scantest where a in ('24610','24611','24612','9999999999');
+----+-------------+----------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
| 1 | SIMPLE | scantest | index | PRIMARY | PRIMARY | 4 | NULL | 24492 | 100.00 | Using where; Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.18 sec)
mysql> explain extended select * from scantest where a in ('24610','24611','24612','2999999999');
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | scantest | range | PRIMARY | PRIMARY | 4 | NULL | 4 | 100.00 | Using where; Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.03 sec)
mysql> flush status;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from scantest where a in ('24610','24611','24612','9999999999');
Empty set (0.02 sec)
mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_next | 24492 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0.04 sec)
mysql> flush status;Query OK, 0 rows affected (0.00 sec)
mysql> select * from scantest where a in ('24610','24611','24612','2999999999');
Empty set (0.00 sec)
mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 4 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0.00 sec)
[27 Jul 2012 9:11]
Valeriy Kravchuk
Looks fixed in current code (mysql-trunk) though:
[openxs@chief trunk]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.0-m10-debug Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table scantest ( a int unsigned not null auto_increment primary key) ;
Query OK, 0 rows affected (0.53 sec)
mysql> insert into scantest (a) values (1), (2), (3);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into scantest (a) select NULL from scantest a, scantest b;
Query OK, 9 rows affected (0.04 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> insert into scantest (a) select NULL from scantest a, scantest b;
Query OK, 144 rows affected (0.05 sec)
Records: 144 Duplicates: 0 Warnings: 0
mysql> insert into scantest (a) select NULL from scantest a, scantest b;
Query OK, 24336 rows affected (3.55 sec)
Records: 24336 Duplicates: 0 Warnings: 0
mysql> explain extended select * from scantest where a in ('24610','24611','24612','9999999999');
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | scantest | range | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using where; Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
[27 Jul 2012 9:12]
Valeriy Kravchuk
So, this is most likely a duplicate of Bug #43319.
[27 Jul 2012 9:13]
Olav Sandstå
This looks like a duplicate of Bug#43319 "Table/index scan when quoting large numbers in an 'IN' of a SELECT ... WHERE".
[27 Jul 2012 18:33]
Gavin Towey
Good news. Thanks for the verification -- I'll try to remember to search for duplicates first.
Description: a query with a where clause like the following: where a in ('1','9999999999') will cause a full table scan. Note that this only happens if the numerica values in the IN list are quoted. Independent of other values in the list, if one of them is greater than 2^32. In this case 9,999,999,999 exceeds that limit. How to repeat: mysql> create table scantest ( a int unsigned not null auto_increment primary key) ; Query OK, 0 rows affected (0.02 sec) mysql> insert into scantest (a) values (1), (2), (3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into scantest (a) select NULL from scantest a, scantest b; Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> insert into scantest (a) select NULL from scantest a, scantest b; Query OK, 144 rows affected (0.01 sec) Records: 144 Duplicates: 0 Warnings: 0 mysql> insert into scantest (a) select NULL from scantest a, scantest b; Query OK, 24336 rows affected (0.13 sec) Records: 24336 Duplicates: 0 Warnings: 0 mysql> select max(a) from scantest; +--------+ | max(a) | +--------+ | 24609 | +--------+ 1 row in set (0.00 sec) -- notice the rows value, it's doing a full table scan -- all values in the list are outside the range of actual values in the table, this query returns zero rows. mysql> explain extended select * from scantest where a in ('24610','24611','24612','9999999999'); +----+-------------+----------+-------+---------------+---------+---------+------+-------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+-------+---------------+---------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | scantest | index | PRIMARY | PRIMARY | 4 | NULL | 25320 | 100.00 | Using where; Using index | +----+-------------+----------+-------+---------------+---------+---------+------+-------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain extended select * from scantest where a in ('24610','24611','24612','2999999999'); +----+-------------+----------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | scantest | range | PRIMARY | PRIMARY | 4 | NULL | 4 | 100.00 | Using where; Using index | +----+-------------+----------+-------+---------------+---------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) Suggested fix: unknown