MySQL 5.7 index scan

gistfile1.txt

mysql [localhost] {msandbox} (test) > Create table test (id int primary key auto_increment, name char(20));
Query OK, 0 rows affected (0.03 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO test VALUES (NULL, REPEAT('a', 20));
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO test VALUES (NULL, REPEAT('a', 20));
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 32 rows affected (0.00 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 64 rows affected (0.00 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 128 rows affected (0.00 sec)
Records: 128  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 256 rows affected (0.00 sec)
Records: 256  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 512 rows affected (0.01 sec)
Records: 512  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 1024 rows affected (0.02 sec)
Records: 1024  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 2048 rows affected (0.03 sec)
Records: 2048  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 4096 rows affected (0.04 sec)
Records: 4096  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 8192 rows affected (0.10 sec)
Records: 8192  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 16384 rows affected (0.14 sec)
Records: 16384  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 32768 rows affected (0.26 sec)
Records: 32768  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 65536 rows affected (0.77 sec)
Records: 65536  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 131072 rows affected (1.04 sec)
Records: 131072  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 262144 rows affected (2.15 sec)
Records: 262144  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 524288 rows affected (4.39 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > INSERT INTO test SELECT NULL, name from test;
Query OK, 1048576 rows affected (9.58 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > explain select * from test order by id limit 1000000, 10.
    -> \c
mysql [localhost] {msandbox} (test) > explain select * from test order by id limit 1000000, 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | PRIMARY | 4       | NULL | 1000010 |   209.15 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > explain select id from test order by id limit 1000000, 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | PRIMARY | 4       | NULL | 1000010 |   209.15 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > explain select id from test order by id limit 1000000, 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | PRIMARY | 4       | NULL | 1000010 |   209.15 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > select id from test order by id limit 1000000, 10;
+---------+
| id      |
+---------+
| 1262118 |
| 1262119 |
| 1262120 |
| 1262121 |
| 1262122 |
| 1262123 |
| 1262124 |
| 1262125 |
| 1262126 |
| 1262127 |
+---------+
10 rows in set (0.21 sec)

mysql [localhost] {msandbox} (test) > select * from test order by id limit 1000000, 10;
+---------+----------------------+
| id      | name                 |
+---------+----------------------+
| 1262118 | aaaaaaaaaaaaaaaaaaaa |
| 1262119 | aaaaaaaaaaaaaaaaaaaa |
| 1262120 | aaaaaaaaaaaaaaaaaaaa |
| 1262121 | aaaaaaaaaaaaaaaaaaaa |
| 1262122 | aaaaaaaaaaaaaaaaaaaa |
| 1262123 | aaaaaaaaaaaaaaaaaaaa |
| 1262124 | aaaaaaaaaaaaaaaaaaaa |
| 1262125 | aaaaaaaaaaaaaaaaaaaa |
| 1262126 | aaaaaaaaaaaaaaaaaaaa |
| 1262127 | aaaaaaaaaaaaaaaaaaaa |
+---------+----------------------+
10 rows in set (0.23 sec)