YzYXvtD9

1E:\mysql-5.7.22-winx64\bin>mysql -uroot test
2Welcome to the MySQL monitor.  Commands end with ; or \g.
3Your MySQL connection id is 2
4Server version: 5.7.22 MySQL Community Server (GPL)
5
6Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
7
8Oracle is a registered trademark of Oracle Corporation and/or its
9affiliates. Other names may be trademarks of their respective
10owners.
11
12Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
13
14mysql> drop table if exists t;
15Query OK, 0 rows affected, 1 warning (0.01 sec)
16
17mysql> create table t(id int not null primary key auto_increment, c1 int not null, c2 int, key idx(c1))engine=innodb;
18Query OK, 0 rows affected (0.08 sec)
19
20mysql> insert t (c1, c2) values (1,1),(1,1),(1,1),(1,1);
21Query OK, 4 rows affected (0.06 sec)
22Records: 4  Duplicates: 0  Warnings: 0
23
24mysql> insert t (c1, c2) select 1, 1 from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10;
25Query OK, 1048576 rows affected (13.36 sec)
26Records: 1048576  Duplicates: 0  Warnings: 0
27
28mysql> insert t (c1, c2) select 4, 1 from t;
29Query OK, 1048580 rows affected (12.04 sec)
30Records: 1048580  Duplicates: 0  Warnings: 0
31
32mysql> insert t (c1, c2) select 4, 1 from t;
33Query OK, 2097160 rows affected (25.86 sec)
34Records: 2097160  Duplicates: 0  Warnings: 0
35
36mysql> analyze table t;
37+--------+---------+----------+----------+
38| Table  | Op      | Msg_type | Msg_text |
39+--------+---------+----------+----------+
40| test.t | analyze | status   | OK       |
41+--------+---------+----------+----------+
421 row in set (1.08 sec)
43
44mysql> flush status;select * from t  where c1 = 4 and id <= 2000000 order by c1 desc, id desc limit 1;show status like 'Handler_read%';
45Query OK, 0 rows affected (0.07 sec)
46
47+---------+----+------+
48| id      | c1 | c2   |
49+---------+----+------+
50| 2000000 |  4 |    1 |
51+---------+----+------+
521 row in set (0.25 sec)
53
54+-----------------------+-------+
55| Variable_name         | Value |
56+-----------------------+-------+
57| Handler_read_first    | 0     |
58| Handler_read_key      | 1     |
59| Handler_read_last     | 0     |
60| Handler_read_next     | 0     |
61| Handler_read_prev     | 0     |
62| Handler_read_rnd      | 0     |
63| Handler_read_rnd_next | 0     |
64+-----------------------+-------+
657 rows in set (0.02 sec)
66
67mysql> flush status;select * from t force key(idx) where c1 = 4 and id <= 2000000 order by c1 desc, id desc limit 1;show status like 'Handler_read%';
68Query OK, 0 rows affected (0.00 sec)
69
70+---------+----+------+
71| id      | c1 | c2   |
72+---------+----+------+
73| 2000000 |  4 |    1 |
74+---------+----+------+
751 row in set (0.00 sec)
76
77+-----------------------+-------+
78| Variable_name         | Value |
79+-----------------------+-------+
80| Handler_read_first    | 0     |
81| Handler_read_key      | 1     |
82| Handler_read_last     | 0     |
83| Handler_read_next     | 0     |
84| Handler_read_prev     | 0     |
85| Handler_read_rnd      | 0     |
86| Handler_read_rnd_next | 0     |
87+-----------------------+-------+
887 rows in set (0.00 sec)
89
90mysql> select version();
91+-----------+
92| version() |
93+-----------+
94| 5.7.22    |
95+-----------+
961 row in set (0.01 sec)
97
98mysql>