DB Indexes
class AddIndexesToTables < ActiveRecord::Migration
def change
add_index :authors_posts, :post_id
add_index :authors_posts, :author_id
add_index :content_references, :site_block_id
add_index :content_references, :post_id
add_index :posts, :category_id
add_index :posts, :author_id
end
end
Vanilla Rails will take you a long way. But in every app, there are places where speed is a big deal. And when you find those places, reducing SQL calls is an easy place to start optimizing." -@justinweiss
source: https://blog.codeship.com/speed-up-activerecord/
Avoid this:
Processing by RestaurantsController#index as HTML
Restaurant Load (1.6ms) SELECT `restaurants`.* FROM `restaurants`
Review Load (1.2ms) SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 1
Review Load (1.2ms) SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 2
Review Load (1.1ms) SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 3
Review Load (1.2ms) SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 4
Review Load (1.2ms) SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 5
Review Load (1.2ms) SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 6
Review Load (1.2ms) SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 7
Review Load (1.0ms) SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 8
Review Load (1.0ms) SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 9
Review Load (1.0ms) SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 10
Go for this:
Restaurant Load (1.2ms) SELECT `restaurants`.* FROM `restaurants`
Review Load (3.0ms) SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
@restaurants = Restaurant.all.includes(:reviews)
@restaurants = Restaurant.all
@restaurants.each do |thing|
"#{thing.review}"
- Stay away from n+1 with eager loading
- When you need optimization and don't care how you get your data, use more sql and less rails helpers
.find_by_sql()
.includes()
User.where(screen_name: ['user1','user2']).blank?
behind the scenes:
1. Query the database for all user data
2. Load the users into an array
3. Check to see if the array size is zero
SELECT "users".* FROM "users" WHERE "users"."screen_name" IN ('user1','user2')
[<#User:0x007fbf6413c510>,<#User:0x007fbf65ab1c70>]
=> true
#blank?
source: http://hashrocket.com/blog/posts/rails-quick-tips-easy-activerecord-optimizations
#empty?
User.where(screen_name: ['user1','user2']).empty?
1. Query database for ONLY a count
SELECT COUNT(*) FROM "users" WHERE "users"."screen_name" IN ('user1','user2')
2. Check to see if the count is zero
=> true
behind the scenes:
So #blank? will load the entire array, then check to see if that array is empty. On the other hand, #empty? asks the db for a count, and checks to see if that number is zero.
#map vs #pluck on an ActiveRecord Relation
#map
User.where(email: ['jane@example.com', 'john@example.com']).map(&:screen_name)
behind the scenes:
1. Query the db for all user data
SELECT "users".* FROM "users" WHERE "users"."email" IN ('jane@example.com','john@example.com')
2. Load users into an array
[<#User:0x007fbf6413c510>,<#User:0x007fbf65ab1c70>]
3. Iterate over array to collect screen_names
['user1','user2']
#pluck
User.where(email: ['jane@example.com', 'john@example.com']).pluck(:screen_name)
behind the scenes:
1. Query the db for ONLY screen_names
SELECT "users"."screen_name" FROM "users"
WHERE "users"."email" IN ('jane@example.com','john@example.com')
2. Return those screen_names in an array
['user1','user2']
So #map will load an entire array, then iterate to collect the screen_names. Alternatively, #pluck asks the database for exactly what it needs and returns an array of just those items.
Don't use #pluck if you're passing in a #where
emails = ['jane@example.com', 'john@example.com']
User.where(screen_name: User.where(email: emails).pluck(:screen_name)).empty?
(using #pluck with #where)
1. Query for just the emails
SELECT "users"."email" FROM "users" WHERE "users"."email" IN ('jane@example.com','john@example.com')
2. Query for the count
SELECT COUNT(*) FROM "users" WHERE "users"."screen_name" IN ('user1','user2')
3. Check if the count is zero
=> true
Instead use #select with a #where
emails = ['jane@example.com', 'john@example.com']
User.where(screen_name: User.where(email: emails).select(:screen_name)).empty?
1. Make one query for count with subquery
SELECT COUNT(*) FROM "users" WHERE "users"."screen_name" IN (
SELECT "users"."screen_name" FROM "users" WHERE "users"."email"
IN ('jane@example.com','john@example.com')
)
2. Check if the count is zero
=> true
1. Use #empty? or #any? instead of #blank or #present?
2. Never use #map on active record relations, use #pluck instead
3. If you're using #pluck to pass values to a #where, use #select instead
On ActiveRecord Associations:
-Phil Karlton
http://blog.pixelastic.com/
Don't do it.
We have control over invalidating the client's cache. All we have to do is change the filename and the browser/server knows that the old file doesn't exist anymore and the new one should be downloaded.