| Bug #71962 | Optimizer prefers table scan when using "in" with value of different type | ||
|---|---|---|---|
| Submitted: | 7 Mar 2014 2:40 | Modified: | 31 Mar 2014 16:26 |
| Reporter: | NOT_FOUND NOT_FOUND | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.6.16 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[7 Mar 2014 8:08]
MySQL Verification Team
Hello!! Thank you for the bug report. Verified as described. Thanks, Umesh
[31 Mar 2014 16:26]
Paul DuBois
Noted in 5.7.5 changelog. For IN() predicates with values different from the key data value, the optimizer sometimes used a table scan when it could do a range scan.
Description: when using "in" of several conditions, if one's type is different from key, optimizer prefers table scan to key scan. How to repeat: mysql [localhost] {msandbox} (test) > create table x (a int primary key, b int) engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql [localhost] {msandbox} (test) > insert into x values (1,1), (2,1), (3,1), (4,1), (5,1), (6,1); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > explain select * from x where a in (1, 2)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: x type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 2 Extra: Using where 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > explain select * from x where a in (1, "2")\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: x type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 6 Extra: Using where 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > explain select * from x where a in ("2")\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: x type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: NULL 1 row in set (0.00 sec)