// 5.6.22 mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.6.22 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.22-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.01 sec) mysql> create table ten(a int); Query OK, 0 rows affected (0.02 sec) mysql> insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> create table one_k(a int); Query OK, 0 rows affected (0.02 sec) mysql> insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; Query OK, 1000 rows affected (0.02 sec) Records: 1000 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE tpk ( -> pk int(11) NOT NULL, -> a int(11) DEFAULT NULL, -> b int(11) DEFAULT NULL, -> PRIMARY KEY (pk), -> KEY a (a) -> ); Query OK, 0 rows affected (0.03 sec) mysql> insert into tpk select a,a,a from one_k; Query OK, 1000 rows affected (0.02 sec) Records: 1000 Duplicates: 0 Warnings: 0 mysql> explain select distinct a from tpk where a<10; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | tpk | range | a | a | 5 | NULL | 9 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.03 sec) mysql> explain select distinct pk from tpk where pk<10; +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | tpk | range | PRIMARY,a | PRIMARY | 4 | NULL | 10 | Using where; Using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain format=json select distinct a from tpk where a<10\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "duplicates_removal": { "using_filesort": false, "table": { "table_name": "tpk", "access_type": "range", "possible_keys": [ "a" ], "key": "a", "used_key_parts": [ "a" ], "key_length": "5", "rows": 9, "filtered": 100, "using_index": true, "attached_condition": "(`test`.`tpk`.`a` < 10)" } } } } 1 row in set, 1 warning (0.00 sec) mysql> explain format=json select distinct pk from tpk where pk<10\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "duplicates_removal": { "using_filesort": false, "table": { "table_name": "tpk", "access_type": "range", "possible_keys": [ "PRIMARY", "a" ], "key": "PRIMARY", "used_key_parts": [ "pk" ], "key_length": "4", "rows": 10, "filtered": 100, "using_index": true, "attached_condition": "(`test`.`tpk`.`pk` < 10)" } } } } 1 row in set, 1 warning (0.00 sec) // 5.7.6 mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.7.6 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.7.6-m16-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.01 sec) mysql> create table ten(a int); Query OK, 0 rows affected (0.03 sec) mysql> insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> create table one_k(a int); Query OK, 0 rows affected (0.02 sec) mysql> insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; Query OK, 1000 rows affected (0.02 sec) Records: 1000 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE tpk ( -> pk int(11) NOT NULL, -> a int(11) DEFAULT NULL, -> b int(11) DEFAULT NULL, -> PRIMARY KEY (pk), -> KEY a (a) -> ); Query OK, 0 rows affected (0.03 sec) mysql> insert into tpk select a,a,a from one_k; Query OK, 1000 rows affected (0.03 sec) Records: 1000 Duplicates: 0 Warnings: 0 mysql> explain select distinct a from tpk where a<10; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | tpk | NULL | range | a | a | 5 | NULL | 9 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select distinct pk from tpk where pk<10; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | tpk | NULL | range | PRIMARY,a | PRIMARY | 4 | NULL | 10 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain format=json select distinct a from tpk where a<10\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "4.62" }, "duplicates_removal": { "using_filesort": false, "table": { "table_name": "tpk", "access_type": "range", "possible_keys": [ "a" ], "key": "a", "used_key_parts": [ "a" ], "key_length": "5", "rows_examined_per_scan": 9, "rows_produced_per_join": 9, "filtered": 100, "using_index": true, "cost_info": { "read_cost": "2.82", "eval_cost": "1.80", "prefix_cost": "4.62", "data_read_per_join": "144" }, "used_columns": [ "pk", "a" ], "attached_condition": "(`test`.`tpk`.`a` < 10)" } } } } 1 row in set, 1 warning (0.00 sec) mysql> explain format=json select distinct pk from tpk where pk<10\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "5.02" }, "duplicates_removal": { "using_filesort": false, "table": { "table_name": "tpk", "access_type": "range", "possible_keys": [ "PRIMARY", "a" ], "key": "PRIMARY", "used_key_parts": [ "pk" ], "key_length": "4", "rows_examined_per_scan": 10, "rows_produced_per_join": 10, "filtered": 100, "using_index": true, "cost_info": { "read_cost": "3.02", "eval_cost": "2.00", "prefix_cost": "5.02", "data_read_per_join": "160" }, "used_columns": [ "pk" ], "attached_condition": "(`test`.`tpk`.`pk` < 10)" } } } } 1 row in set, 1 warning (0.00 sec)