This is just a translation of my former, explanation-less post in this Apr.
It still reproduces with 5.7.7-rc. Straightforwardly I should report a bug, but I find this: http://www.tocker.ca/2015/07/15/the-mysql-5-7-optimizer-challenge.html -- I would like a polo :)
procedure
% cat /etc/lsb-release DISTRIB_ID=Ubuntu DISTRIB_RELEASE=14.04 DISTRIB_CODENAME=trusty DISTRIB_DESCRIPTION="Ubuntu 14.04.2 LTS" % dpkg -l|grep mysql ii mysql-apt-config 0.3.5-1ubuntu14.04 all Auto configuration for MySQL APT Repo. ii mysql-client 5.7.7-rc-1ubuntu14.04 amd64 MySQL Client meta package depending on latest version ii mysql-common 5.7.7-rc-1ubuntu14.04 amd64 MySQL configuration for client and server ii mysql-community-client 5.7.7-rc-1ubuntu14.04 amd64 MySQL Client and client tools ii mysql-community-server 5.7.7-rc-1ubuntu14.04 amd64 MySQL Server and server tools
% seq 1000000 > seq.out
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.7-rc MySQL Community Server (GPL) Copyright (c) 2000, 2015, 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 database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> create table foo1 (id int, col1 int default null, col2 int default null, primary key (id), key (col1)) engine=innodb; Query OK, 0 rows affected (0.02 sec) mysql> create table foo2 (id int, col1 int default null, col2 int default null, primary key (id), key (col1)) engine=myisam; Query OK, 0 rows affected (0.00 sec) mysql> load data local infile '/home/ichii386/seq.out' into table foo1 (id); Query OK, 1000000 rows affected (7.85 sec) Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0 mysql> load data local infile '/home/ichii386/seq.out' into table foo2 (id); Query OK, 1000000 rows affected (2.42 sec) Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0
mysql> explain select sql_no_cache * from foo1 where id in (1,2,3,4,5,6,7,8,9,10) and col1 is null; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | foo1 | NULL | ref | PRIMARY,col1 | col1 | 5 | const | 10 | 0.50 | Using index condition | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select sql_no_cache * from foo1 where id in (1,2,3,4,5,6,7,8,9,10) and col1 is null; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 1 | NULL | NULL | | 2 | NULL | NULL | | 3 | NULL | NULL | | 4 | NULL | NULL | | 5 | NULL | NULL | | 6 | NULL | NULL | | 7 | NULL | NULL | | 8 | NULL | NULL | | 9 | NULL | NULL | | 10 | NULL | NULL | +----+------+------+ 10 rows in set (0.12 sec) mysql> explain select sql_no_cache * from foo2 where id in (1,2,3,4,5,6,7,8,9,10) and col1 is null; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | foo2 | NULL | range | PRIMARY,col1 | PRIMARY | 4 | NULL | 10 | 100.00 | Using index condition; Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select sql_no_cache * from foo2 where id in (1,2,3,4,5,6,7,8,9,10) and col1 is null; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 1 | NULL | NULL | | 2 | NULL | NULL | | 3 | NULL | NULL | | 4 | NULL | NULL | | 5 | NULL | NULL | | 6 | NULL | NULL | | 7 | NULL | NULL | | 8 | NULL | NULL | | 9 | NULL | NULL | | 10 | NULL | NULL | +----+------+------+ 10 rows in set (0.00 sec)
It use pk with foo2 of MyISAM, but not with foo1 of InnoDB, resulted with 0.12sec.