comments テーブルの post_id と created_at で複合インデックスを作成する

[d-shimizu]

filesort が行われている

mysql> explain select * from comments where post_id = 100 order by created_at desc limit 3;
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | comments | NULL       | ref  | post_id_idx   | post_id_idx | 4       | const |    5 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

isucon@ip-10-128-1-252:~/private_isu.git/benchmarker$ ./bin/benchmarker -u ./userdata -t http://10.128.1.80
{"pass":true,"score":14612,"success":13311,"fail":0,"messages":[]}

[d-shimizu]

mysql> alter table comments drop index post_id_idx, add index post_id_idx (post_id, created_at);
Query OK, 0 rows affected (1.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from comments where post_id = 100 order by created_at desc limit 3;
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------+
| id | select_type | table    | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra               |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------+
|  1 | SIMPLE      | comments | NULL       | ref  | post_id_iex   | post_id_iex | 4       | const |    5 |   100.00 | Backward index scan |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)

isucon@ip-10-128-1-252:~/private_isu.git/benchmarker$ ./bin/benchmarker -u ./userdata -t http://10.128.1.80
{"pass":true,"score":16181,"success":14790,"fail":0,"messages":[]}

[d-shimizu]

mysql> alter table comments drop index post_id_idx, add index post_id_idx(post_id, created_at desc);
Query OK, 0 rows affected (0.62 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from comments where post_id = 100 order by created_at desc limit 3;
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | comments | NULL       | ref  | post_id_idx   | post_id_idx | 4       | const |    5 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

isucon@ip-10-128-1-252:~/private_isu.git/benchmarker$ ./bin/benchmarker -u ./userdata -t http://10.128.1.80
{"pass":true,"score":17015,"success":15600,"fail":0,"messages":[]}