Ac#veRecord*N+1*count*queries 2015/07/15'Shibuya.rb @k0kubun

I'm$@k0kubun DevInfra)at)Cookpad

Let's&think&about N+1$count$queries

How$do$you$defeat$this? User.all.each do |user| user.tweets.count end # SELECT `users`.* FROM `users` # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 1 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 2 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 3 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 4 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 5

OK,$cache$it$:) User.all.each do |user| user.tweets_count end class Tweet < ActiveRecord::Base belongs_to :user, counter_cache: true end # SELECT `users`.* FROM `users` ͦΕ͕!Rails!way

OK...?

ʊਓਓਓਓਓਓਓਓਓਓʊ ʼɹALTER&TABLE&usersɹʻ ʉY^Y^Y^Y^Y^Y^Y^Y^Yʉ ΑͬΆͲݫ͘͠ͳ͍ͱ΍Γͨ͘ͳ͍

ແݶʹ૿͑ଓ͚ΔΧϥϜ CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `tweets_count` int(11) NOT NULL DEFAULT '0', `replies_count` int(11) NOT NULL DEFAULT '0', `favorites_count` int(11) NOT NULL DEFAULT '0', `retweets_count` int(11) NOT NULL DEFAULT '0', `followers_count` int(11) NOT NULL DEFAULT '0', `following_count` int(11) NOT NULL DEFAULT '0', -- …

ͦͯ͠๚ΕΔഁ୼ ਨΕྲྀ͞ΕΔN+1ΫΤϦ

ͦΜͳ೰ΈΛղܾ͢Δ ࠷ߴͷGemΛ࡞Γ·ͨ͠

ac#verecord)precount N+1$count$query$killer$for$Ac3veRecord

͜ΕΛͳ User.all.each do |user| user.tweets.count end # SELECT `users`.* FROM `users` # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 1 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 2 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 3 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 4 # SELECT COUNT(*) FROM `tweets` WHERE `tweets`.`user_id` = 5

͜͏͡Ό User.all.precount(:tweets).each do |user| user.tweets.count end # SELECT `users`.* FROM `users` # SELECT COUNT(`tweets`.`user_id`), `tweets`.`user_id` FROM # `tweets` WHERE `tweets`.`user_id` IN (1, 2, 3, 4, 5) # GROUP BY `tweets`.`user_id`

JOIN΋؆୯ User.all.eager_count(:tweets).each do |user| user.tweets.count end # SELECT `users`.`id` AS t0_r0, `users`.`created_at` AS t0_r3, # `users`.`updated_at` AS t0_r4, COUNT(`tweets`.`id`) AS t1_r0 # FROM `users` LEFT OUTER JOIN `tweets` # ON `tweets`.`user_id` = `users`.`id` GROUP BY users.id

ͦͯ͠଎͍ counter_cacheͳΜ͍ͯΒͳ͔ͬͨΜ΍

Just%write gem$"ac$verecord*precount"