Re: The MySQL 5.7 Optimizer Challenge

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.