init

$ perl -e 'for ($n= 1; $n <= 1000000; $n++) {printf("%d\n", $n);}' > /tmp/seq

$ mysql

mysql56> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.6.24-log |
+------------+
1 row in set (0.00 sec)

mysql56> SELECT @@mroonga_version;
+-------------------+
| @@mroonga_version |
+-------------------+
| 5.00              |
+-------------------+
1 row in set (0.00 sec)

mysql56> create database mrn;
Query OK, 1 row affected (0.03 sec)

mysql56> use mrn
Database changed

mysql56> create table storage (num int unsigned primary key) Engine = Mroonga;
Query OK, 0 rows affected (0.04 sec)

mysql56> create table wrapper (num int unsigned primary key) Engine = Mroonga Comment 'engine "MyISAM"';
Query OK, 0 rows affected (0.05 sec)

mysql56> LOAD DATA INFILE '/tmp/seq' INTO TABLE storage;
Query OK, 1000000 rows affected (2.85 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql56> LOAD DATA INFILE '/tmp/seq' INTO TABLE wrapper;
Query OK, 1000000 rows affected (4.23 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

sleep

mysql56> explain SELECT * FROM storage WHERE num > 100;
1 row in set (0.57 sec) -- 初回は遅い
1 row in set (0.09 sec) -- 2回目はちょっと速い
1 row in set (0.00 sec)
1 row in set (0.09 sec)
1 row in set (1.49 sec) -- しばらく放置していたら遅くなった
1 row in set (0.99 sec) -- 連打すると速い
1 row in set (0.09 sec)
1 row in set (0.09 sec)
1 row in set (0.09 sec)
1 row in set (0.09 sec)
1 row in set (0.09 sec)
1 row in set (0.09 sec)
1 row in set (0.09 sec)
1 row in set (0.09 sec)
1 row in set (0.09 sec)

mysql56> SELECT sleep(60);
+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.22 sec)

mysql56> explain SELECT * FROM storage WHERE num > 100; -- 60秒おいたらまた遅くなってる
+----+-------------+---------+-------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows    | Extra                    |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | storage | index | PRIMARY       | PRIMARY | 4       | NULL | 1000000 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+--------------------------+
1 row in set (0.67 sec)

storage

mysql56> explain SELECT * FROM storage WHERE num < 100;
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | storage | range | PRIMARY       | PRIMARY | 4       | NULL |   99 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.21 sec)

mysql56> explain SELECT * FROM storage WHERE num > 100;
+----+-------------+---------+-------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows    | Extra                    |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | storage | index | PRIMARY       | PRIMARY | 4       | NULL | 1000000 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+--------------------------+
1 row in set (0.63 sec)

mysql56> SHOW profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.027399 |
| checking permissions | 0.000021 |
| Opening tables       | 0.006335 |
| init                 | 0.001271 |
| System lock          | 0.000022 |
| optimizing           | 0.000017 |
| statistics           | 0.439639 |
| preparing            | 0.000070 |
| explaining           | 0.039687 |
| query end            | 0.000023 |
| closing tables       | 0.000037 |
| freeing items        | 0.000494 |
| cleaning up          | 0.000032 |
+----------------------+----------+
13 rows in set, 1 warning (0.06 sec)