comments テーブルの post_id カラムのインデックスを作成する

[d-shimizu]

isucon@ip-10-128-1-127:~/private_isu.git/benchmarker$ ./bin/benchmarker -u ./userdata -t http://10.128.1.100
{"pass":true,"score":529,"success":543,"fail":5,"messages":["リクエストがタイムアウトしました (GET /favicon.ico)","リクエストがタイムアウトしました (POST /login)","リクエストがタイムアウトしました (POST /register)"]}

$ sudo mysqldumpslow /var/log/mysql/mysql-slow.log
:
Count: 1084  Time=0.05s (51s)  Lock=0.00s (0s)  Rows=2.7 (2929), isuconp[isuconp]@localhost
  SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N

mysql> EXPLAIN select * from comments where post_id = 9995 order by created_at desc limit 3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: comments
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 99791
     filtered: 10.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

[d-shimizu]

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

[d-shimizu]

mysql> EXPLAIN select * from comments where post_id = 9995 order by created_at desc limit 3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: comments
   partitions: NULL
         type: ref
possible_keys: post_id_idx
          key: post_id_idx
      key_len: 4
          ref: const
         rows: 6
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

[d-shimizu]

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