Bug #74256 index_extensions used incorrectly in select cause performance regression

• Submitted: 7 Oct 2014 22:43
• Modified: 14 Jan 2015 8:39
• Reporter: Gavin Towey
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.6.19, 5.6.22
• OS: Any
• CPU Architecture: Any

[7 Oct 2014 22:43] Gavin Towey

Description:
New optimizer feature index_extensions is used incorrectly in some queries: http://dev.mysql.com/doc/refman/5.6/en/index-extensions.html

The optimizer will choose an appropriate secondary index, but then fail to use the second part of the index for purposes of filling the WHERE clause.  This is shown by a change in the key_len and a query type=ref instead of type=range

How to repeat:
-- create a table w/ around 3 million rows:
create table index_cond_test ( id bigint unsigned not null auto_increment primary key, flag int unsigned, name varchar(255),  index (flag) );

insert into index_cond_test values (NULL, 0, MD5(RAND()) ), (NULL, 0, MD5(RAND())), (NULL, 0, MD5(RAND())), (NULL,0, MD5(RAND()));
insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a, index_cond_test b;
insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a, index_cond_test b;
insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;

mysql> select count(*) from index_cond_test;
+----------+
| count(*) |
+----------+
|  2829120 |
+----------+
1 row in set (0.49 sec)

-- now customize the data a bit; the issue is only seen when cardinality on the secondary index is around a certain value.
-- first try: flag column cardinality is about 1% of total rows in the table
mysql> update index_cond_test SET flag=IF(RAND()<.01, UNIX_TIMESTAMP(NOW()-INTERVAL RAND()*400000 MINUTE), 0), name=MD5(RAND());

Query OK, 2829120 rows affected, 1 warning (41.99 sec)
Rows matched: 2829120  Changed: 2829120  Warnings: 1

mysql> show indexes from index_cond_test;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_cond_test |          0 | PRIMARY  |            1 | id          | A         |     2823549 |     NULL | NULL   |      | BTREE      |         |               |
| index_cond_test |          1 | flag     |            1 | flag        | A         |     1411774 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.02 sec)

-- not right, let's update them

mysql> analyze table index_cond_test;
+---------------------+---------+----------+----------+
| Table               | Op      | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| tmp.index_cond_test | analyze | status   | OK       |
+---------------------+---------+----------+----------+
1 row in set (0.01 sec)

-- this is correct
mysql> show indexes from index_cond_test;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_cond_test |          0 | PRIMARY  |            1 | id          | A         |     2815391 |     NULL | NULL   |      | BTREE      |         |               |
| index_cond_test |          1 | flag     |            1 | flag        | A         |       55203 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

-- Here's the problem query. This is an explain plan which makes sense:
mysql> explain select id from index_cond_test WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra                       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | index_cond_test | range | PRIMARY,flag  | PRIMARY | 8       | NULL |    4 | Using where; Using filesort |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+
1 row in set (0.01 sec)

-- Now for the failing case.  Change the cardinality of the secondary index to be about 20% of the total rows
mysql> update index_cond_test SET flag=IF(RAND()<.2, UNIX_TIMESTAMP(NOW()-INTERVAL RAND()*400000 MINUTE), 0);
Query OK, 795049 rows affected, 1 warning (19.22 sec)
Rows matched: 2829120  Changed: 795049  Warnings: 1

mysql> analyze table index_cond_test; show indexes from index_cond_test;
+---------------------+---------+----------+----------+
| Table               | Op      | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| tmp.index_cond_test | analyze | status   | OK       |
+---------------------+---------+----------+----------+
1 row in set (0.02 sec)

+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_cond_test |          0 | PRIMARY  |            1 | id          | A         |     2815391 |     NULL | NULL   |      | BTREE      |         |               |
| index_cond_test |          1 | flag     |            1 | flag        | A         |      703847 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

-- Here's the incorrect explain plan.  key_len=5 means it's not using the full index:
mysql> explain select id from index_cond_test WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id;
+----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref   | rows | Extra                                              |
+----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | index_cond_test | ref  | PRIMARY,flag  | flag | 5       | const |    4 | Using index condition; Using where; Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+
1 row in set (0.02 sec)

-- a USE INDEX (flag) shows the right explain plan (notice key_len=13)
mysql> explain select id from index_cond_test use index (flag) WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id;
+----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+
| id | select_type | table           | type  | possible_keys | key  | key_len | ref  | rows | Extra                                 |
+----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | index_cond_test | range | flag          | flag | 13      | NULL |    4 | Using index condition; Using filesort |
+----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+
1 row in set (0.01 sec)

Suggested fix:
Unknown.  It's unclear to me why the optimizer fails to use the full length of the index in this case.  The ORDER BY clause seems to play some part, but that's not clear either.

[14 Jan 2015 7:47] Valeriy Kravchuk

I think this is a 5.6-specific kind of http://bugs.mysql.com/bug.php?id=12113 (with ICP used).

[14 Jan 2015 8:39] MySQL Verification Team

Hello Gavin Towey,

Thank you for the report and test case.

Thanks,
Umesh

[14 Jan 2015 8:40] MySQL Verification Team

[test]> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.22                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.22                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

[(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
[test]> create table index_cond_test ( id bigint unsigned not null auto_increment primary key, flag int unsigned, name varchar(255),  index (flag) );
Query OK, 0 rows affected (0.05 sec)

[test]> insert into index_cond_test values (NULL, 0, MD5(RAND()) ), (NULL, 0, MD5(RAND())), (NULL, 0, MD5(RAND())), (NULL,0, MD5(RAND()));
insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a, index_cond_test b;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a, index_cond_test b;
Query OK, 16 rows affected (0.03 sec)
Records: 16  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a, index_cond_test b;
Query OK, 400 rows affected (0.01 sec)
Records: 400  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 420 rows affected (0.02 sec)
Records: 420  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;Query OK, 840 rows affected (0.03 sec)
Records: 840  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 1680 rows affected (0.06 sec)
Records: 1680  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 3360 rows affected (0.09 sec)
Records: 3360  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 6720 rows affected (0.18 sec)
Records: 6720  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 13440 rows affected (0.43 sec)
Records: 13440  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 26880 rows affected (0.46 sec)
Records: 26880  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 53760 rows affected (2.15 sec)
Records: 53760  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 107520 rows affected (1.77 sec)
Records: 107520  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 215040 rows affected (3.47 sec)
Records: 215040  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 430080 rows affected (6.92 sec)
Records: 430080  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a limit 1000000;
Query OK, 860160 rows affected (13.95 sec)
Records: 860160  Duplicates: 0  Warnings: 0

[test]> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a limit 1000000;
Query OK, 1000000 rows affected (19.88 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

[test]> select count(*) from index_cond_test;
+----------+
| count(*) |
+----------+
|  2720320 |
+----------+
1 row in set (15.34 sec)

[test]> update index_cond_test SET flag=IF(RAND()<.01, UNIX_TIMESTAMP(NOW()-INTERVAL RAND()*400000 MINUTE), 0), name=MD5(RAND());
Query OK, 2720320 rows affected (1 min 29.59 sec)
Rows matched: 2720320  Changed: 2720320  Warnings: 0

[test]> show indexes from index_cond_test;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_cond_test |          0 | PRIMARY  |            1 | id          | A         |     2714829 |     NULL | NULL   |      | BTREE      |         |               |
| index_cond_test |          1 | flag     |            1 | flag        | A         |     1357414 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

[test]> analyze table index_cond_test;
+----------------------+---------+----------+----------+
| Table                | Op      | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| test.index_cond_test | analyze | status   | OK       |
+----------------------+---------+----------+----------+
1 row in set (0.01 sec)

[test]> show indexes from index_cond_test;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_cond_test |          0 | PRIMARY  |            1 | id          | A         |     2707185 |     NULL | NULL   |      | BTREE      |         |               |
| index_cond_test |          1 | flag     |            1 | flag        | A         |       52061 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

[14 Jan 2015 8:40] MySQL Verification Team

[test]> explain select id from index_cond_test WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra                       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | index_cond_test | range | PRIMARY,flag  | PRIMARY | 8       | NULL |    4 | Using where; Using filesort |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+
1 row in set (0.03 sec)

[test]> update index_cond_test SET flag=IF(RAND()<.2, UNIX_TIMESTAMP(NOW()-INTERVAL RAND()*400000 MINUTE), 0);
Query OK, 565163 rows affected (52.55 sec)
Rows matched: 2720320  Changed: 565163  Warnings: 0

[test]> analyze table index_cond_test; show indexes from index_cond_test;
+----------------------+---------+----------+----------+
| Table                | Op      | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| test.index_cond_test | analyze | status   | OK       |
+----------------------+---------+----------+----------+
1 row in set (0.01 sec)

+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_cond_test |          0 | PRIMARY  |            1 | id          | A         |     2707185 |     NULL | NULL   |      | BTREE      |         |               |
| index_cond_test |          1 | flag     |            1 | flag        | A         |      676796 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

[test]> explain select id from index_cond_test WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id;
+----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref   | rows | Extra                                              |
+----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | index_cond_test | ref  | PRIMARY,flag  | flag | 5       | const |    4 | Using index condition; Using where; Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

[test]> explain select id from index_cond_test use index (flag) WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id;
+----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+
| id | select_type | table           | type  | possible_keys | key  | key_len | ref  | rows | Extra                                 |
+----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | index_cond_test | range | flag          | flag | 13      | NULL |    4 | Using index condition; Using filesort |
+----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

[14 Jan 2015 9:42] MySQL Verification Team

// 5.6.24

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.24                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.24-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> create table index_cond_test ( id bigint unsigned not null auto_increment primary key, flag int unsigned, name varchar(255),  index (flag) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into index_cond_test values (NULL, 0, MD5(RAND()) ), (NULL, 0, MD5(RAND())), (NULL, 0, MD5(RAND())), (NULL,0, MD5(RAND()));
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a, index_cond_test b;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a, index_cond_test b;
Query OK, 400 rows affected (0.00 sec)
Records: 400  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 420 rows affected (0.01 sec)
Records: 420  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 840 rows affected (0.00 sec)
Records: 840  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 1680 rows affected (0.01 sec)
Records: 1680  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 3360 rows affected (0.02 sec)
Records: 3360  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 6720 rows affected (0.04 sec)
Records: 6720  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 13440 rows affected (0.09 sec)
Records: 13440  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 26880 rows affected (0.17 sec)
Records: 26880  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 53760 rows affected (0.33 sec)
Records: 53760  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 107520 rows affected (0.64 sec)
Records: 107520  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 215040 rows affected (1.36 sec)
Records: 215040  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a;
Query OK, 430080 rows affected (2.56 sec)
Records: 430080  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a limit 1000000;
Query OK, 860160 rows affected (5.11 sec)
Records: 860160  Duplicates: 0  Warnings: 0

mysql> insert into index_cond_test  select NULL, 0, 0  FROM index_cond_test a limit 1000000;
Query OK, 1000000 rows affected (5.81 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> select count(*) from index_cond_test;
+----------+
| count(*) |
+----------+
|  2720320 |
+----------+
1 row in set (0.53 sec)

mysql> update index_cond_test SET flag=IF(RAND()<.01, UNIX_TIMESTAMP(NOW()-INTERVAL RAND()*400000 MINUTE), 0), name=MD5(RAND());
Query OK, 2720320 rows affected (47.56 sec)
Rows matched: 2720320  Changed: 2720320  Warnings: 0

mysql> show indexes from index_cond_test;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_cond_test |          0 | PRIMARY  |            1 | id          | A         |     2714829 |     NULL | NULL   |      | BTREE      |         |               |
| index_cond_test |          1 | flag     |            1 | flag        | A         |     1357414 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> analyze table index_cond_test;
+----------------------+---------+----------+----------+
| Table                | Op      | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| test.index_cond_test | analyze | status   | OK       |
+----------------------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> show indexes from index_cond_test;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_cond_test |          0 | PRIMARY  |            1 | id          | A         |     2707185 |     NULL | NULL   |      | BTREE      |         |               |
| index_cond_test |          1 | flag     |            1 | flag        | A         |       52061 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

[14 Jan 2015 9:42] MySQL Verification Team

// 5.6.24

mysql> explain select id from index_cond_test WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra                       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | index_cond_test | range | PRIMARY,flag  | PRIMARY | 8       | NULL |    4 | Using where; Using filesort |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> update index_cond_test SET flag=IF(RAND()<.2, UNIX_TIMESTAMP(NOW()-INTERVAL RAND()*400000 MINUTE), 0);
Query OK, 565521 rows affected (19.91 sec)
Rows matched: 2720320  Changed: 565521  Warnings: 0

mysql> analyze table index_cond_test; show indexes from index_cond_test;
+----------------------+---------+----------+----------+
| Table                | Op      | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| test.index_cond_test | analyze | status   | OK       |
+----------------------+---------+----------+----------+
1 row in set (0.03 sec)

+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_cond_test |          0 | PRIMARY  |            1 | id          | A         |     2707185 |     NULL | NULL   |      | BTREE      |         |               |
| index_cond_test |          1 | flag     |            1 | flag        | A         |      676796 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> explain select id from index_cond_test WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id;
+----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref   | rows | Extra                                              |
+----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | index_cond_test | ref  | PRIMARY,flag  | flag | 5       | const |    4 | Using index condition; Using where; Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select id from index_cond_test use index (flag) WHERE flag=FALSE AND id in ('10521571','10521559','10521555','10521535') ORDER BY name, id;
+----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+
| id | select_type | table           | type  | possible_keys | key  | key_len | ref  | rows | Extra                                 |
+----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | index_cond_test | range | flag          | flag | 13      | NULL |    4 | Using index condition; Using filesort |
+----+-------------+-----------------+-------+---------------+------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

[2 Nov 2015 6:19] Øystein Grøvlen

Posted by developer:
 
Since MySQL 5.7.5, the given test case no longer use the bad query plan.  With WL#7339 (Use improved records per key estimate interface in optimizer), InnoDB statistics are more precise, and the PRIMARY index will be selected. I think that will be the case regardless of the cardinality of the flag column.