query explains for perf analysis

log

context: https://lobste.rs/s/5hshvd/proposal_lobste_rs_performance_analysis#c_bp5fdv

# rails console to collect queries:
irb(main):001:0> u = User.find_by(username: 'pushcx');nil
D, [2019-10-02T14:04:06.531843 #12101] DEBUG -- :    (0.6ms)  SET NAMES utf8mb4,  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
D, [2019-10-02T14:04:06.546456 #12101] DEBUG -- :   User Load (1.0ms)  SELECT  `users`.* FROM `users` WHERE `users`.`username` = 'pushcx' LIMIT 1
=> nil
irb(main):002:0> Comment.for_user(u).order("id DESC").includes(:user, :hat, :story => :user).joins(:story).where.not(stories: { is_expired: true }).limit(20) .offset((1 - 1) * 20);nil
D, [2019-10-02T14:05:35.270496 #12101] DEBUG -- :   Comment Load (4609.9ms)  SELECT  `comments`.* FROM `comments` INNER JOIN `stories` ON `stories`.`id` = `comments`.`story_id` WHERE `stories`.`is_expired` != TRUE ORDER BY id DESC LIMIT 11 OFFSET 0
D, [2019-10-02T14:05:35.298837 #12101] DEBUG -- :   User Load (1.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` IN (11453, 10438, 4896, 9062, 10655, 10302, 5845, 11291, 5199)
D, [2019-10-02T14:05:35.305279 #12101] DEBUG -- :   Story Load (0.9ms)  SELECT `stories`.* FROM `stories` WHERE `stories`.`id` IN (58321, 58308, 58316, 58296, 58294, 58256, 58301)
D, [2019-10-02T14:05:35.329672 #12101] DEBUG -- :   User Load (1.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` IN (8543, 5485, 10627, 2213, 3163, 9494, 756)
=> nil
irb(main):003:0> ReplyingComment.where(user_id: u.id, is_unread: true).count;nil
D, [2019-10-02T14:06:13.134450 #12101] DEBUG -- :    (1097.6ms)  SELECT COUNT(*) FROM `replying_comments` WHERE `replying_comments`.`user_id` = 78 AND `replying_comments`.`is_unread` = TRUE
=> nil

# mariadb console to explain:
MariaDB [lobsters]> explain SELECT  `comments`.* FROM `comments` INNER JOIN `stories` ON `stories`.`id` = `comments`.`story_id` WHERE `stories`.`is_expired` != TRUE ORDER BY id DESC LIMIT 11 OFFSET 0;
+------+-------------+----------+-------+----------------------------------------------+-----------------------------+---------+---------------------+-------+-----------------------------------------------------------+
| id   | select_type | table    | type  | possible_keys                                | key                         | key_len | ref                 | rows  | Extra                                                     |
+------+-------------+----------+-------+----------------------------------------------+-----------------------------+---------+---------------------+-------+-----------------------------------------------------------+
|    1 | SIMPLE      | stories  | range | PRIMARY,is_idxes,index_stories_on_is_expired | index_stories_on_is_expired | 1       | NULL                | 24887 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | comments | ref   | story_id_short_id                            | story_id_short_id           | 8       | lobsters.stories.id |     3 |                                                           |
+------+-------------+----------+-------+----------------------------------------------+-----------------------------+---------+---------------------+-------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [lobsters]> explain SELECT COUNT(*) FROM `replying_comments` WHERE `replying_comments`.`user_id` = 78 AND `replying_comments`.`is_unread` = TRUE;
+------+--------------------+-----------------+--------+---------------------------------------------------------------------------------+-------------------------------+---------+-------------------------------------------------------------+------+-------------+
| id   | select_type        | table           | type   | possible_keys                                                                   | key                           | key_len | ref                                                         | rows | Extra       |
+------+--------------------+-----------------+--------+---------------------------------------------------------------------------------+-------------------------------+---------+-------------------------------------------------------------+------+-------------+
|    1 | PRIMARY            | read_ribbons    | ref    | index_read_ribbons_on_user_id,index_read_ribbons_on_story_id                    | index_read_ribbons_on_user_id | 8       | const                                                       | 4660 | Using where |
|    1 | PRIMARY            | stories         | eq_ref | PRIMARY,index_stories_on_user_id                                                | PRIMARY                       | 8       | lobsters.read_ribbons.story_id                              |    1 | Using where |
|    1 | PRIMARY            | comments        | ref    | story_id_short_id,index_comments_on_user_id,downvote_index                      | story_id_short_id             | 8       | lobsters.read_ribbons.story_id                              |    3 | Using where |
|    1 | PRIMARY            | parent_comments | eq_ref | PRIMARY                                                                         | PRIMARY                       | 8       | lobsters.comments.parent_comment_id                         |    1 | Using where |
|    5 | DEPENDENT SUBQUERY | f               | ref    | user_id_comment_id,user_id_story_id,index_votes_on_comment_id,votes_story_id_fk | user_id_story_id              | 16      | lobsters.parent_comments.user_id,lobsters.comments.story_id |    1 | Using where |
|    5 | DEPENDENT SUBQUERY | c               | eq_ref | PRIMARY,index_comments_on_user_id,downvote_index                                | PRIMARY                       | 8       | lobsters.f.comment_id                                       |    1 | Using where |
|    4 | DEPENDENT SUBQUERY | votes           | ref    | user_id_comment_id,user_id_story_id,index_votes_on_comment_id                   | user_id_comment_id            | 17      | lobsters.read_ribbons.user_id,lobsters.comments.id          |    1 |             |
|    3 | DEPENDENT SUBQUERY | votes           | ref    | user_id_comment_id,user_id_story_id,index_votes_on_comment_id                   | user_id_comment_id            | 17      | lobsters.read_ribbons.user_id,lobsters.comments.id          |    1 |             |
+------+--------------------+-----------------+--------+---------------------------------------------------------------------------------+-------------------------------+---------+-------------------------------------------------------------+------+-------------+
8 rows in set (0.01 sec)

MariaDB [lobsters]> explain SELECT COUNT(*) FROM `replying_comments` WHERE `replying_comments`.`user_id` = 78 AND `replying_comments`.`is_unread` = TRUE;
+------+--------------------+-----------------+--------+---------------------------------------------------------------------------------+-------------------------------+---------+-------------------------------------------------------------+------+-------------+
| id   | select_type        | table           | type   | possible_keys                                                                   | key                           | key_len | ref                                                         | rows | Extra       |
+------+--------------------+-----------------+--------+---------------------------------------------------------------------------------+-------------------------------+---------+-------------------------------------------------------------+------+-------------+
|    1 | PRIMARY            | read_ribbons    | ref    | index_read_ribbons_on_user_id,index_read_ribbons_on_story_id                    | index_read_ribbons_on_user_id | 8       | const                                                       | 4926 | Using where |
|    1 | PRIMARY            | stories         | eq_ref | PRIMARY,index_stories_on_user_id                                                | PRIMARY                       | 8       | lobsters.read_ribbons.story_id                              |    1 | Using where |
|    1 | PRIMARY            | comments        | ref    | story_id_short_id,index_comments_on_user_id,downvote_index                      | story_id_short_id             | 8       | lobsters.read_ribbons.story_id                              |    3 | Using where |
|    1 | PRIMARY            | parent_comments | eq_ref | PRIMARY                                                                         | PRIMARY                       | 8       | lobsters.comments.parent_comment_id                         |    1 | Using where |
|    5 | DEPENDENT SUBQUERY | f               | ref    | user_id_comment_id,user_id_story_id,index_votes_on_comment_id,votes_story_id_fk | user_id_story_id              | 16      | lobsters.parent_comments.user_id,lobsters.comments.story_id |    1 | Using where |
|    5 | DEPENDENT SUBQUERY | c               | eq_ref | PRIMARY,index_comments_on_user_id,downvote_index                                | PRIMARY                       | 8       | lobsters.f.comment_id                                       |    1 | Using where |
|    4 | DEPENDENT SUBQUERY | votes           | ref    | user_id_comment_id,user_id_story_id,index_votes_on_comment_id                   | user_id_comment_id            | 17      | lobsters.read_ribbons.user_id,lobsters.comments.id          |    1 |             |
|    3 | DEPENDENT SUBQUERY | votes           | ref    | user_id_comment_id,user_id_story_id,index_votes_on_comment_id                   | user_id_comment_id            | 17      | lobsters.read_ribbons.user_id,lobsters.comments.id          |    1 |             |
+------+--------------------+-----------------+--------+---------------------------------------------------------------------------------+-------------------------------+---------+-------------------------------------------------------------+------+-------------+
8 rows in set (0.00 sec)