Skip to content

Instantly share code, notes, and snippets.

@pushcx

pushcx/comparison.txt

Last active January 7, 2021 02:20
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save pushcx/7f36f51e8c262eb9758373d634a1d930 to your computer and use it in GitHub Desktop.
Save pushcx/7f36f51e8c262eb9758373d634a1d930 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
comparison.txt
# old mariadb:
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 | 2878 | Using where |
| 1 | PRIMARY | stories | eq_ref | PRIMARY,index_stories_on_user_id,index_stories_on_id_and_is_expired,index_stories_on_score | PRIMARY | 8 | lobsters.read_ribbons.story_id | 1 | Using where |
| 1 | PRIMARY | comments | ref | story_id_short_id,index_comments_on_user_id,index_comments_on_score | story_id_short_id | 8 | lobsters.read_ribbons.story_id | 4 | 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 | 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.009 sec)
MariaDB [lobsters]> analyze 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 | r_rows | filtered | r_filtered | 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 | 2878 | 2878.00 | 100.00 | 100.00 | Using where |
| 1 | PRIMARY | stories | eq_ref | PRIMARY,index_stories_on_user_id,index_stories_on_id_and_is_expired,index_stories_on_score | PRIMARY | 8 | lobsters.read_ribbons.story_id | 1 | 1.00 | 50.00 | 89.19 | Using where |
| 1 | PRIMARY | comments | ref | story_id_short_id,index_comments_on_user_id,index_comments_on_score | story_id_short_id | 8 | lobsters.read_ribbons.story_id | 4 | 13.26 | 26.35 | 34.51 | Using where |
| 1 | PRIMARY | parent_comments | eq_ref | PRIMARY | PRIMARY | 8 | lobsters.comments.parent_comment_id | 1 | 0.72 | 100.00 | 100.00 | 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 | 1.00 | 100.00 | 100.00 | Using where |
| 5 | DEPENDENT SUBQUERY | c | eq_ref | PRIMARY,index_comments_on_user_id | PRIMARY | 8 | lobsters.f.comment_id | 1 | 1.00 | 100.00 | 100.00 | 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 | NULL | 100.00 | NULL | |
| 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 | NULL | 100.00 | NULL | |
+------+--------------------+-----------------+--------+--------------------------------------------------------------------------------------------+-------------------------------+---------+-------------------------------------------------------------+------+---------+----------+------------+-------------+
8 rows in set (2.059 sec)
# new mysql prod:
mysql> explain SELECT COUNT(*) FROM `replying_comments` WHERE `replying_comments`.`user_id` = 78 AND `replying_comments`.`is_unread` = TRUE;
+----+--------------------+-----------------+------------+--------+--------------------------------------------------------------------------------------------+--------------------------------+---------+-------------------------------------------------------------+--------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-----------------+------------+--------+--------------------------------------------------------------------------------------------+--------------------------------+---------+-------------------------------------------------------------+--------+----------+-------------------------+
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 12 | const,const | 3 | 100.00 | NULL |
| 2 | DERIVED | comments | NULL | ALL | story_id_short_id,index_comments_on_score | NULL | NULL | NULL | 298926 | 0.50 | Using where |
| 2 | DERIVED | stories | NULL | eq_ref | PRIMARY,index_stories_on_user_id,index_stories_on_id_and_is_expired,index_stories_on_score | PRIMARY | 8 | lobsters.comments.story_id | 1 | 50.00 | Using where |
| 2 | DERIVED | parent_comments | NULL | eq_ref | PRIMARY | PRIMARY | 8 | lobsters.comments.parent_comment_id | 1 | 100.00 | Using where |
| 2 | DERIVED | read_ribbons | NULL | ref | index_read_ribbons_on_user_id,index_read_ribbons_on_story_id | index_read_ribbons_on_story_id | 8 | lobsters.comments.story_id | 55 | 0.09 | Using where |
| 2 | DERIVED | f | NULL | 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 | 100.00 | Using where; Not exists |
| 2 | DERIVED | c | NULL | eq_ref | PRIMARY,index_comments_on_user_id | PRIMARY | 8 | lobsters.f.comment_id | 1 | 100.00 | Using where |
| 4 | DEPENDENT SUBQUERY | votes | NULL | 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 | 3 | 100.00 | NULL |
| 5 | DEPENDENT SUBQUERY | votes | NULL | 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 | 3 | 100.00 | NULL |
+----+--------------------+-----------------+------------+--------+--------------------------------------------------------------------------------------------+--------------------------------+---------+-------------------------------------------------------------+--------+----------+-------------------------+
9 rows in set, 8 warnings (0.03 sec)
mysql> explain analyze SELECT COUNT(*) FROM `replying_comments` WHERE `replying_comments`.`user_id` = 78 AND `replying_comments`.`is_unread` = TRUE;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------+
| EXPLAIN
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------+
| -> Aggregate: count(0) (actual time=73487.149..73487.149 rows=1 loops=1)
-> Index lookup on replying_comments using <auto_key0> (user_id=78, is_unread=true) (actual time=0.014..0.014 rows=0 loops=1)
-> Materialize (actual time=73487.144..73487.144 rows=0 loops=1)
-> Nested loop antijoin (cost=97616.09 rows=66) (actual time=29.705..67837.623 rows=73558 loops=1)
-> Nested loop inner join (cost=97543.68 rows=37) (actual time=29.687..61593.853 rows=74463 loops=1)
-> Filter: ((parent_comments.id is null) or ((parent_comments.is_deleted = 0) and (parent_comments.is_moderated = 0) and (parent_comments.score >= 0))) (cost=51963.85 rows=747
) (actual time=2.669..4829.674 rows=281979 loops=1)
-> Nested loop left join (cost=51963.85 rows=747) (actual time=2.667..4737.439 rows=286692 loops=1)
-> Nested loop inner join (cost=51141.90 rows=747) (actual time=2.660..3589.931 rows=286692 loops=1)
-> Filter: ((comments.is_moderated = 0) and (comments.is_deleted = 0) and (comments.score >= 0)) (cost=50554.59 rows=1495) (actual time=2.155..2660.885 rows=288905
loops=1)
-> Table scan on comments (cost=50554.59 rows=298936) (actual time=2.149..2576.659 rows=297932 loops=1)
-> Filter: (stories.score >= 0) (cost=0.29 rows=0) (actual time=0.003..0.003 rows=1 loops=288905)
-> Single-row index lookup on stories using PRIMARY (id=comments.story_id) (cost=0.29 rows=1) (actual time=0.003..0.003 rows=1 loops=288905)
-> Single-row index lookup on parent_comments using PRIMARY (id=comments.parent_comment_id) (cost=1.00 rows=1) (actual time=0.004..0.004 rows=1 loops=286692)
-> Filter: ((read_ribbons.is_following = 1) and (comments.user_id <> read_ribbons.user_id) and ((read_ribbons.user_id = parent_comments.user_id) or ((read_ribbons.user_id = sto
ries.user_id) and (parent_comments.user_id is null)))) (cost=55.44 rows=0) (actual time=0.073..0.201 rows=0 loops=281979)
-> Index lookup on read_ribbons using index_read_ribbons_on_story_id (story_id=comments.story_id) (cost=55.44 rows=55) (actual time=0.042..0.185 rows=89 loops=281979)
-> Nested loop inner join (cost=67.78 rows=2) (actual time=0.083..0.083 rows=0 loops=74463)
-> Filter: (f.vote < 0) (cost=1.77 rows=2) (actual time=0.078..0.081 rows=0 loops=74463)
-> Index lookup on f using user_id_story_id (user_id=parent_comments.user_id, story_id=comments.story_id) (cost=1.77 rows=2) (actual time=0.059..0.080 rows=4 loops=74463)
-> Filter: (c.user_id = comments.user_id) (cost=1.76 rows=1) (actual time=0.022..0.022 rows=0 loops=7553)
-> Single-row index lookup on c using PRIMARY (id=f.comment_id) (cost=1.76 rows=1) (actual time=0.021..0.021 rows=1 loops=7553)
-> Select #5 (subquery in projection; dependent)
-> Index lookup on votes using user_id_comment_id (user_id=read_ribbons.user_id, comment_id=comments.id) (cost=3.45 rows=3) (actual time=0.052..0.052 rows=0 loops=73558)
-> Select #4 (subquery in projection; dependent)
-> Index lookup on votes using user_id_comment_id (user_id=read_ribbons.user_id, comment_id=comments.id) (cost=3.45 rows=3) (actual time=0.005..0.005 rows=0 loops=73558)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------+
1 row in set, 7 warnings (1 min 13.54 sec)
Raw
pushdown-attempt.txt
# new prod db
ysql> with rc as (select "read_ribbons"."user_id" AS "user_id","comments"."id" AS "comment_id","read_ribbons"."story_id" AS "story_id","comments"."parent_comment_id" AS "parent_comment_id","comments"."created_at" AS "comment_create
d_at","parent_comments"."user_id" AS "parent_comment_author_id","comments"."user_id" AS "comment_author_id","stories"."user_id" AS "story_author_id",("read_ribbons"."updated_at" < "comments"."created_at") AS "is_unread",(select "vot
es"."vote" from "votes" where (("votes"."user_id" = "read_ribbons"."user_id") and ("votes"."comment_id" = "comments"."id"))) AS "current_vote_vote",(select "votes"."reason" from "votes" where (("votes"."user_id" = "read_ribbons"."us
er_id") and ("votes"."comment_id" = "comments"."id"))) AS "current_vote_reason" from ((("read_ribbons" join "comments" on(("comments"."story_id" = "read_ribbons"."story_id"))) join "stories" on(("stories"."id" = "comments"."story_id
")))
" = 0) and ("comments"."is_moderated" = 0) and (("parent_comments"."user_id" = "read_ribbons"."user_id") or (("parent_comments"."user_id" is null) and ("stories"."user_id" = "read_ribbons"."user_id"))) and ("stories"."score" >= 0) a
nd ("comments"."score" >= 0) and (("parent_comments"."id" is null) or (("parent_comments"."score" >= 0) and ("parent_comments"."is_moderated" = 0) and ("parent_comments"."is_deleted" = 0))) and exists(select 1 from ("votes" "f" join
"comme
count(*) from rc where `replying_comments`.`user_id` = 78 AND `replying_comments`.`is_unread` = TRUE;
ERROR 1054 (42S22): Unknown column 'replying_comments.user_id' in 'where clause'
mysql> with rc as (select "read_ribbons"."user_id" AS "user_id","comments"."id" AS "comment_id","read_ribbons"."story_id" AS "story_id","comments"."parent_comment_id" AS "parent_comment_id","comments"."created_at" AS "comment_created_at","parent_comments"."user_id" AS "parent_comment_author_id","comments"."user_id" AS "comment_author_id","stories"."user_id" AS "story_author_id",("read_ribbons"."updated_at" < "comments"."created_at") AS "is_unread",(select "votes"."vote" from "votes" where (("votes"."user_id" = "read_ribbons"."user_id") and ("votes"."comment_id" = "comments"."id"))) AS "current_vote_vote",(select "votes"."reason" from "votes" where (("votes"."user_id" = "read_ribbons"."user_id") and ("votes"."comment_id" = "comments"."id"))) AS "current_vote_reason" from ((("read_ribbons" join "comments" on(("comments"."story_id" = "read_ribbons"."story_id"))) join "stories" on(("stories"."id" = "comments"."story_id"))) left join "comments" "parent_comments" on(("parent_comments"."id" = "comments"."parent_comment_id"))) where (("read_ribbons"."is_following" = 1) and ("comments"."user_id" <> "read_ribbons"."user_id") and ("comments"."is_deleted" = 0) and ("comments"."is_moderated" = 0) and (("parent_comments"."user_id" = "read_ribbons"."user_id") or (("parent_comments"."user_id" is null) and ("stories"."user_id" = "read_ribbons"."user_id"))) and ("stories"."score" >= 0) and ("comments"."score" >= 0) and (("parent_comments"."id" is null) or (("parent_comments"."score" >= 0) and ("parent_comments"."is_moderated" = 0) and ("parent_comments"."is_deleted" = 0))) and exists(select 1 from ("votes" "f" join "comments" "c" on(("f"."comment_id" = "c"."id"))) where (("f"."vote" < 0) and ("f"."user_id" = "parent_comments"."user_id") and ("c"."user_id" = "comments"."user_id") and ("f"."story_id" = "comments"."story_id"))) is false)) select count(*) from rc where `rc`.`user_id` = 78 AND `rc`.`is_unread` = TRUE;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (1 min 54.00 sec)
mysql> explain with rc as (select "read_ribbons"."user_id" AS "user_id","comments"."id" AS "comment_id","read_ribbons"."story_id" AS "story_id","comments"."parent_comment_id" AS "parent_comment_id","comments"."created_at" AS "commen
t_created_at","parent_comments"."user_id" AS "parent_comment_author_id","comments"."user_id" AS "comment_author_id","stories"."user_id" AS "story_author_id",("read_ribbons"."updated_at" < "comments"."created_at") AS "is_unread",(sel
ect "votes"."vote" from "votes" where (("votes"."user_id" = "read_ribbons"."user_id") and ("votes"."comment_id" = "comments"."id"))) AS "current_vote_vote",(select "votes"."reason" from "votes" where (("votes"."user_id" = "read_ribb
ons"."user_id") and ("votes"."comment_id" = "comments"."id"))) AS "current_vote_reason" from ((("read_ribbons" join "comments" on(("comments"."story_id" = "read_ribbons"."story_id"))) join "stories" on(("stories"."id" = "comments"."
story_id"))) left join "comments" "parent_comments" on(("parent_comments"."id" = "comments"."parent_comment_id"))) where (("read_ribbons"."is_following" = 1) and ("comments"."user_id" <> "read_ribbons"."user_id") and ("comments"."is
_deleted" = 0) and ("comments"."is_moderated" = 0) and (("parent_comments"."user_id" = "read_ribbons"."user_id") or (("parent_comments"."user_id" is null) and ("stories"."user_id" = "read_ribbons"."user_id"))) and ("stories"."score"
>= 0) and ("comments"."score" >= 0) and (("parent_comments"."id" is null) or (("parent_comments"."score" >= 0) and ("parent_comments"."is_moderated" = 0) and ("parent_comments"."is_deleted" = 0))) and exists(select 1 from ("votes"
"f" join "comments" "c" on(("f"."comment_id" = "c"."id"))) where (("f"."vote" < 0) and ("f"."user_id" = "parent_comments"."user_id") and ("c"."user_id" = "comments"."user_id") and ("f"."story_id" = "comments"."story_id"))) is false))
+----+--------------------+-----------------+------------+--------+--------------------------------------------------------------------------------------------+--------------------------------+---------+-------------------------------------------------------------+--------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-----------------+------------+--------+--------------------------------------------------------------------------------------------+--------------------------------+---------+-------------------------------------------------------------+--------+----------+-------------------------+
| 1 | PRIMARY | | NULL | ref | | | 12 | const,const | 3 | 100.00 | NULL |
| 2 | DERIVED | comments | NULL | ALL | story_id_short_id,index_comments_on_score | NULL | NULL | NULL | 299186 | 0.50 | Using where |
| 2 | DERIVED | stories | NULL | eq_ref | PRIMARY,index_stories_on_user_id,index_stories_on_id_and_is_expired,index_stories_on_score | PRIMARY | 8 | lobsters.comments.story_id | 1 | 50.00 | Using where |
| 2 | DERIVED | parent_comments | NULL | eq_ref | PRIMARY | PRIMARY | 8 | lobsters.comments.parent_comment_id | 1 | 100.00 | Using where |
| 2 | DERIVED | read_ribbons | NULL | ref | index_read_ribbons_on_user_id,index_read_ribbons_on_story_id | index_read_ribbons_on_story_id | 8 | lobsters.comments.story_id | 55 | 0.09 | Using where |
| 2 | DERIVED | f | NULL | 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 | 100.00 | Using where; Not exists |
| 2 | DERIVED | c | NULL | eq_ref | PRIMARY,index_comments_on_user_id | PRIMARY | 8 | lobsters.f.comment_id | 1 | 100.00 | Using where |
| 4 | DEPENDENT SUBQUERY | votes | NULL | 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 | 3 | 100.00 | NULL |
| 3 | DEPENDENT SUBQUERY | votes | NULL | 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 | 3 | 100.00 | NULL |
+----+--------------------+-----------------+------------+--------+--------------------------------------------------------------------------------------------+--------------------------------+---------+-------------------------------------------------------------+--------+----------+-------------------------+
9 rows in set, 8 warnings (0.01 sec)
# rewritten to not use any subqueries (also dropped the 'count(*)' to grab the rows):
mysql> explain select "read_ribbons"."user_id" AS "user_id","comments"."id" AS "comment_id","read_ribbons"."story_id" AS "story_id","comments"."parent_comment_id" AS "parent_comment_id","comments"."created_at" AS "comment_created_at
","parent_comments"."user_id" AS "parent_comment_author_id","comments"."user_id" AS "comment_author_id","stories"."user_id" AS "story_author_id",("read_ribbons"."updated_at" < "comments"."created_at") AS "is_unread",(select "votes".
"vote" from "votes" where (("votes"."user_id" = "read_ribbons"."user_id") and ("votes"."comment_id" = "comments"."id"))) AS "current_vote_vote",(select "votes"."reason" from "votes" where (("votes"."user_id" = "read_ribbons"."user_i
d") and ("votes"."comment_id" = "comments"."id"))) AS "current_vote_reason" from ((("read_ribbons" join "comments" on(("comments"."story_id" = "read_ribbons"."story_id"))) join "stories" on(("stories"."id" = "comments"."story_id")))
left join "comments" "parent_comments" on(("parent_comments"."id" = "comments"."parent_comment_id"))) where (("read_ribbons"."is_following" = 1) and ("comments"."user_id" <> "read_ribbons"."user_id") and ("comments"."is_deleted" =
0) and ("comments"."is_moderated" = 0) and (("parent_comments"."user_id" = "read_ribbons"."user_id") or (("parent_comments"."user_id" is null) and ("stories"."user_id" = "read_ribbons"."user_id"))) and ("stories"."score" >= 0) and (
"comme
mme
bbo
+----+--------------------+-----------------+------------+--------+--------------------------------------------------------------------------------------------+-------------------------------+---------+-----------------------------------------------------------------+------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-----------------+------------+--------+--------------------------------------------------------------------------------------------+-------------------------------+---------+-----------------------------------------------------------------+------+----------+-------------------------+
| 1 | PRIMARY | read_ribbons | NULL | ref | index_read_ribbons_on_user_id,index_read_ribbons_on_story_id | index_read_ribbons_on_user_id | 8 | const | 2878 | 10.00 | Using where |
| 1 | PRIMARY | stories | NULL | eq_ref | PRIMARY,index_stories_on_user_id,index_stories_on_id_and_is_expired,index_stories_on_score | PRIMARY | 8 | lobsters.read_ribbons.story_id | 1 | 50.00 | Using where |
| 1 | PRIMARY | comments | NULL | ref | story_id_short_id,index_comments_on_user_id,index_comments_on_score | story_id_short_id | 8 | lobsters.read_ribbons.story_id | 8 | 0.59 | Using where |
| 1 | PRIMARY | parent_comments | NULL | eq_ref | PRIMARY | PRIMARY | 8 | lobsters.comments.parent_comment_id | 1 | 10.02 | Using where |
| 1 | PRIMARY | f | NULL | 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.read_ribbons.story_id | 1 | 100.00 | Using where; Not exists |
| 1 | PRIMARY | c | NULL | eq_ref | PRIMARY,index_comments_on_user_id | PRIMARY | 8 | lobsters.f.comment_id | 1 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | votes | NULL | 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 | 3 | 100.00 | NULL |
| 2 | DEPENDENT SUBQUERY | votes | NULL | 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 | 3 | 100.00 | NULL |
+----+--------------------+-----------------+------------+--------+--------------------------------------------------------------------------------------------+-------------------------------+---------+-----------------------------------------------------------------+------+----------+-------------------------+
8 rows in set, 8 warnings (0.01 sec)
# explain analyze of previous
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop antijoin (cost=4429.71 rows=13) (actual time=492.489..492.489 rows=0 loops=1)
-> Filter: (((parent_comments.user_id = 78) or ((stories.user_id = 78) and (parent_comments.user_id is null))) and ((parent_comments.id is null) or ((parent_comments.is_deleted = 0) and (parent_comments.is_moderated = 0) and (parent_comments.score >= 0)))) (cost=4427.17 rows=7) (actual time=415.175..492.264 rows=1 loops=1)
-> Nested loop left join (cost=4427.17 rows=7) (actual time=90.836..490.315 rows=12032 loops=1)
-> Nested loop inner join (cost=4412.80 rows=7) (actual time=90.823..461.325 rows=12032 loops=1)
-> Nested loop inner join (cost=3077.48 rows=144) (actual time=5.375..83.108 rows=2575 loops=1)
-> Filter: (read_ribbons.is_following = 1) (cost=2906.23 rows=288) (actual time=5.315..70.287 rows=2878 loops=1)
-> Index lookup on read_ribbons using index_read_ribbons_on_user_id (user_id=78) (cost=2906.23 rows=2878) (actual time=5.310..69.869 rows=2878 loops=1)
-> Filter: (stories.score >= 0) (cost=0.50 rows=0) (actual time=0.004..0.004 rows=1 loops=2878)
-> Single-row index lookup on stories using PRIMARY (id=read_ribbons.story_id) (cost=0.50 rows=1) (actual time=0.004..0.004 rows=1 loops=2878)
-> Filter: ((comments.is_moderated = 0) and (comments.is_deleted = 0) and (comments.user_id <> 78) and (comments.score >= 0) and (read_ribbons.updated_at < comments.created_at)) (cost=8.44 rows=0) (actual time=0.113..0.146 rows=5 loops=2575)
-> Index lookup on comments using story_id_short_id (story_id=read_ribbons.story_id) (cost=8.44 rows=8) (actual time=0.078..0.143 rows=13 loops=2575)
-> Single-row index lookup on parent_comments using PRIMARY (id=comments.parent_comment_id) (cost=1.00 rows=1) (actual time=0.002..0.002 rows=1 loops=12032)
-> Nested loop inner join (cost=3.46 rows=2) (actual time=0.222..0.222 rows=1 loops=1)
-> Filter: (f.vote < 0) (cost=2.01 rows=2) (actual time=0.187..0.187 rows=1 loops=1)
-> Index lookup on f using user_id_story_id (user_id=parent_comments.user_id, story_id=read_ribbons.story_id) (cost=2.01 rows=2) (actual time=0.185..0.185 rows=1 loops=1)
-> Filter: (c.user_id = comments.user_id) (cost=1.90 rows=1) (actual time=0.035..0.035 rows=1 loops=1)
-> Single-row index lookup on c using PRIMARY (id=f.comment_id) (cost=1.90 rows=1) (actual time=0.034..0.034 rows=1 loops=1)
-> Select #2 (subquery in projection; dependent)
-> Index lookup on votes using user_id_comment_id (user_id=read_ribbons.user_id, comment_id=comments.id) (cost=3.46 rows=3) (never executed)
-> Select #3 (subquery in projection; dependent)
-> Index lookup on votes using user_id_comment_id (user_id=read_ribbons.user_id, comment_id=comments.id) (cost=3.46 rows=3) (never executed)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 7 warnings (0.51 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment