CODEWITHSUNDEEP
ruby-on-railscachingoptimizationquery-optimization
kittyminky
kittyminky

Reputation: 485

optimal activerecord query format for number of X

If for example a Tweet has_many :favorites, is it faster to call Tweet.favorites.size or to have a separate attribute/column in the database for a Tweet such as num_of_favs. While my initial thought was that Tweet.favorites.size was faster, I know that certain Rails optimization techniques detail creating your own counter_cache methods such as the Tweet model having a favorites_size attribute which isn't much different from num_of_favs. I am using Postgres btw.

Upvotes: 0

Views: 38

Answers (1)

pixelearth
pixelearth

Reputation: 14630

I did the following console tests on some of my dbs. One has nearly 60,000 rows. Granted, this is not enormous by any means.

My tests show that counting the rows of this table are 30 times slower than retrieving a value from a row. But when you're talking about .4ms or 12ms, for me personally this is not worth the optimization for my projects. The last one is on an association. You can see the times followed by ms.

Loading development environment (Rails 4.0.1)
Loading ~/.railsrc
2.0.0-p247 :001 > Log.count
   (12.3ms)  SELECT COUNT(*) FROM `logs`
58133

and

2.0.0-p247 :002 > Log.first.method
  Log Load (0.4ms)  SELECT `logs`.* FROM `logs` ORDER BY `logs`.`id` ASC LIMIT 1
"GET"
2.0.0-p247 :003 >

and

1.8.7-p352 :005 > Pack.last.lessons.count
  Pack Load (0.6ms)  SELECT `packs`.* FROM `packs` ORDER BY packs.id DESC LIMIT 1
  SQL (2.8ms)  SELECT COUNT(*) FROM `lessons` INNER JOIN `lessons_packs` ON `lessons`.id = `lessons_packs`.lesson_id WHERE ((`lessons_packs`.pack_id = 460))
 => 103

Upvotes: 1

Related Questions