Add indexes to comments

[nyancat3]

Initial score

{"pass":true,"score":78404,"success":74572,"fail":0,"messages":[]}

slow query log

# mysqldumpslow -s t /var/log/mysql/mysql-slow.log

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 421627  Time=0.00s (19s)  Lock=0.00s (0s)  Rows=0.5 (201843), 2users@localhost
  #

Count: 74118  Time=0.00s (12s)  Lock=0.00s (0s)  Rows=0.4 (27453), isuconp[isuconp]@localhost
  SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N

Count: 312  Time=0.04s (11s)  Lock=0.00s (0s)  Rows=1.0 (312), isuconp[isuconp]@localhost
  SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = N

Count: 75646  Time=0.00s (11s)  Lock=0.00s (0s)  Rows=1.0 (75646), isuconp[isuconp]@localhost
  SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = N

[nyancat3]

[1] explain

mysql> explain SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = 1;
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | comments | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100167 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

CREATE INDEX

CREATE INDEX `idx_user_id` on `comments` (`user_id`);

[nyancat3]

explain

mysql> explain SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = 1;
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | comments | NULL       | ref  | idx_user_id   | idx_user_id | 4       | const |  101 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

score: 78404 -> 80237

{"pass":true,"score":80237,"success":76244,"fail":0,"messages":[]}

[nyancat3]

[2]

explain

mysql> explain SELECT * FROM `comments` WHERE `post_id` = 1 ORDER BY `created_at` DESC LIMIT 20;
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | comments | NULL       | ref  | idx_post_id   | idx_post_id | 4       | const |   12 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

CREATE INDEX

CREATE INDEX `idx_post_id_created_at` on `comments` (`post_id`, `created_at`);

[nyancat3]

explain

mysql> explain SELECT * FROM `comments` WHERE `post_id` = 1 ORDER BY `created_at` DESC LIMIT 20;
+----+-------------+----------+------------+------+------------------------------------+------------------------+---------+-------+------+----------+---------------------+
| id | select_type | table    | partitions | type | possible_keys                      | key                    | key_len | ref   | rows | filtered | Extra               |
+----+-------------+----------+------------+------+------------------------------------+------------------------+---------+-------+------+----------+---------------------+
|  1 | SIMPLE      | comments | NULL       | ref  | idx_post_id,idx_post_id_created_at | idx_post_id_created_at | 4       | const |   12 |   100.00 | Backward index scan |
+----+-------------+----------+------------+------+------------------------------------+------------------------+---------+-------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)

score 80237 -> 80175 :(

{"pass":true,"score":80175,"success":76174,"fail":0,"messages":[]}

[nyancat3]

slow query log

Details

```bash # mysqldumpslow -s t /var/log/mysql/mysql-slow.log Reading mysql slow query log from /var/log/mysql/mysql-slow.log Count: 437560 Time=0.00s (20s) Lock=0.00s (0s) Rows=0.5 (223732), 2users@localhost # Count: 76146 Time=0.00s (12s) Lock=0.00s (0s) Rows=0.4 (29030), isuconp[isuconp]@localhost SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N Count: 77776 Time=0.00s (11s) Lock=0.00s (0s) Rows=1.0 (77776), isuconp[isuconp]@localhost SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = N Count: 351 Time=0.03s (9s) Lock=0.00s (0s) Rows=10.2 (3566), isuconp[isuconp]@localhost SELECT `posts`.`id`, `posts`.`user_id`, `posts`.`body`, `posts`.`mime`, `posts`.`created_at`, `users`.`account_name` FROM `posts` JOIN `users` ON `posts`.`user_id` = `users`.`id` WHERE `user_id` = N AND `users`.`del_flg` = N ORDER BY `created_at` DESC LIMIT N Count: 46238 Time=0.00s (5s) Lock=0.00s (0s) Rows=1.0 (46238), isuconp[isuconp]@localhost SELECT * FROM `users` WHERE `id` = N Count: 7127 Time=0.00s (2s) Lock=0.00s (0s) Rows=1.0 (7127), isuconp[isuconp]@localhost SELECT * FROM `posts` WHERE `id` = N Count: 351 Time=0.00s (1s) Lock=0.00s (0s) Rows=10.2 (3567), isuconp[isuconp]@localhost SELECT `id` FROM `posts` WHERE `user_id` = N Count: 259 Time=0.01s (1s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost INSERT INTO `users` (`account_name`, `passhash`) VALUES ('S','S') Count: 232 Time=0.01s (1s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost INSERT INTO `comments` (`post_id`, `user_id`, `comment`) VALUES ('S',N,'S') Count: 3429 Time=0.00s (1s) Lock=0.00s (0s) Rows=20.0 (68580), isuconp[isuconp]@localhost SELECT `posts`.`id`, `posts`.`user_id`, `posts`.`body`, `posts`.`created_at`, `posts`.`mime`, `users`.`account_name` FROM `posts` JOIN `users` ON `posts`.`user_id` = `users`.`id` WHERE `users`.`del_flg` = N ORDER BY `created_at` DESC LIMIT N Count: 199 Time=0.01s (1s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost INSERT INTO `posts` (`user_id`, `mime`, `imgdata`, `body`) VALUES (N,'S','S','S') Count: 1630 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1630), isuconp[isuconp]@localhost SELECT `posts`.`id`, `posts`.`user_id`, `posts`.`mime`, `posts`.`imgdata`, `posts`.`body`, `posts`.`created_at`, `users`.`account_name` FROM `posts` JOIN `users` ON `posts`.`user_id` = `users`.`id` WHERE `posts`.`id` = 'S' LIMIT N Count: 1630 Time=0.00s (0s) Lock=0.00s (0s) Rows=8.7 (14165), isuconp[isuconp]@localhost SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC Count: 1918 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.8 (1575), isuconp[isuconp]@localhost SELECT * FROM users WHERE account_name = 'S' AND del_flg = N Count: 200 Time=0.00s (0s) Lock=0.00s (0s) Rows=20.0 (4000), isuconp[isuconp]@localhost SELECT `posts`.`id`, `posts`.`user_id`, `posts`.`body`, `posts`.`mime`, `posts`.`created_at`, `users`.`account_name` FROM `posts` JOIN `users` ON `posts`.`user_id` = `users`.`id` WHERE `posts`.`created_at` <= 'S' AND `users`.`del_flg` = N ORDER BY `created_at` DESC LIMIT N Count: 1 Time=0.06s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost FLUSH LOGS Count: 351 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (351), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = N Count: 351 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (351), isuconp[isuconp]@localhost SELECT * FROM `users` WHERE `account_name` = 'S' AND `del_flg` = N Count: 50 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (50), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N) Count: 43 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (43), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N) Count: 49 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (49), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N) Count: 259 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost SELECT N FROM users WHERE `account_name` = 'S' Count: 35 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (35), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N) Count: 37 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (37), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N) Count: 32 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (32), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N) Count: 24 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (24), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 18 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (18), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 16 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (16), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 20 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (20), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N) Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost DELETE FROM users WHERE id > N Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost DELETE FROM comments WHERE id > N Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost UPDATE users SET del_flg = N WHERE id % N = N Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost UPDATE users SET del_flg = N Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconp[isuconp]@localhost DELETE FROM posts WHERE id > N Count: 6 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (6), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 8 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (8), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N) Count: 6 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (6), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N) Count: 3 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (3), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), isuconp[isuconp]@localhost SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N) Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts administrator command: Quit Count: 4 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts administrator command: Ping Count: 218752 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts administrator command: Close stmt Count: 218803 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts administrator command: Prepare ```