ActiveRecord vs Arel command comparison

activerecord vs arel command comparison.mysql

-- Story.base.positive_ranked.joins(:taggings).where(taggings: { tag_id: tags.map(&:id) })

EXPLAIN SELECT `stories`.*
FROM   `stories`
       INNER JOIN `taggings`
               ON `taggings`.`story_id` = `stories`.`id`
WHERE  `stories`.`merged_story_id` IS NULL
       AND `stories`.`is_expired` = false
       AND ( ( Cast(upvotes AS signed) - Cast(downvotes AS signed) ) >= 0 )
       AND `taggings`.`tag_id` = 159;

-- +----+-------------+----------+------------+--------+-------------------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------+--------------------------+
-- | id | select_type | table    | partitions | type   | possible_keys                                                                 | key             | key_len | ref                            | rows | filtered | Extra                    |
-- +----+-------------+----------+------------+--------+-------------------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------+--------------------------+
-- |  1 | SIMPLE      | taggings | NULL       | index  | story_id_tag_id                                                               | story_id_tag_id | 8       | NULL                           | 1000 |    10.00 | Using where; Using index |
-- |  1 | SIMPLE      | stories  | NULL       | eq_ref | PRIMARY,is_idxes,index_stories_on_is_expired,index_stories_on_merged_story_id | PRIMARY         | 4       | lobsters_dev.taggings.story_id |    1 |    98.61 | Using where              |
-- +----+-------------+----------+------------+--------+-------------------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------+--------------------------+
-- 2 rows in set, 1 warning (0.00 sec)


-- Story.base.positive_ranked.where(
--   Story.arel_table[:id].in(
--     Tagging.arel_table.where(
--       Tagging.arel_table[:tag_id].in(tags.map(&:id))
--     ).project(
--       Tagging.arel_table[:story_id]
--     )
--   )
-- )

EXPLAIN SELECT `stories`.*
FROM   `stories`
WHERE  `stories`.`merged_story_id` IS NULL
       AND `stories`.`is_expired` = false
       AND ( ( Cast(upvotes AS signed) - Cast(downvotes AS signed) ) >= 0 )
       AND `stories`.`id` IN (SELECT `taggings`.`story_id`
                              FROM   `taggings`
                              WHERE  `taggings`.`tag_id` IN ( 159 ));

-- +----+-------------+----------+------------+--------+-------------------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------+--------------------------+
-- | id | select_type | table    | partitions | type   | possible_keys                                                                 | key             | key_len | ref                            | rows | filtered | Extra                    |
-- +----+-------------+----------+------------+--------+-------------------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------+--------------------------+
-- |  1 | SIMPLE      | taggings | NULL       | index  | story_id_tag_id                                                               | story_id_tag_id | 8       | NULL                           | 1000 |    10.00 | Using where; Using index |
-- |  1 | SIMPLE      | stories  | NULL       | eq_ref | PRIMARY,is_idxes,index_stories_on_is_expired,index_stories_on_merged_story_id | PRIMARY         | 4       | lobsters_dev.taggings.story_id |    1 |    98.61 | Using where              |
-- +----+-------------+----------+------------+--------+-------------------------------------------------------------------------------+-----------------+---------+--------------------------------+------+----------+--------------------------+
-- 2 rows in set, 1 warning (0.00 sec)