Create a gist now

Instantly share code, notes, and snippets.

@rmm5t /OUTPUT.md
Last active Feb 24, 2017

What would you like to do?
How to properly introduce a new counter_cache to an existing Rails project.
class AddCommentsCountToPosts < ActiveRecord::Migration
def change
change_table :posts do |t|
t.integer :comments_count, default: 0
end
reversible do |dir|
dir.up { fast }
end
end
# DO THIS!
def fast
execute <<-SQL.squish
UPDATE posts
SET comments_count = (SELECT count(1)
FROM comments
WHERE comments.post_id = posts.id);
SQL
end
# NOT THIS!
def slow
Post.reset_column_information
say_with_time "Seeding posts.comments_count -- Better grab a coffee." do
print " -> "
Post.select(:id).find_in_batches do |posts|
print "."
posts.each do |s|
Post.reset_counters s.id, :comments
end
end
puts
Post.count
end
end
end

Fast/efficient approach:

-- execute("UPDATE posts SET comments_count = (SELECT count(1) FROM comments WHERE comments.post_id = posts.id)")
   -> 1.3197s

Slow/naïve approach:

-- Seeding posts.comments_count -- Better grab a coffee.
   -> ...........................
   -> 144.7302s
   -> 26900 rows
@levberlin

This is great! Will have to read up on my raw SQL to fully understand, but certainly faster.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment