Skip to content

Instantly share code, notes, and snippets.

@rmm5t
Last active August 19, 2024 15:05
Show Gist options
  • Save rmm5t/5aa63288fc5ab858e718 to your computer and use it in GitHub Desktop.
Save rmm5t/5aa63288fc5ab858e718 to your computer and use it in GitHub Desktop.
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
Copy link

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

@williantenfen
Copy link

nice!

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