How can I make these Active Record queries faster?

I’m using Active Record to send a few queries to a postgres database.

I have a User model, a Business model and a joiner named UserBiz.

The queries I mentioned go through the entire UserBiz collection and then filter out the businesses that match user provided categories and searches.

 if !params[:category].blank?
    dataset = UserBiz.all.includes(:business, :user).select{|ub| (ub.business.category.downcase == params[:category].downcase) }
 else 
    dataset = UserBiz.all.includes(:business, :user)
 end

 if !params[:search].blank?
    dataset = dataset.select{|ub| (ub.business.summary.downcase.include? params[:search].downcase) || (ub.business.name.downcase.include? params[:search].downcase) }
 end

These “work” but the problem is when I threw a quarter million UserBizs into my database to see what happens, one search or category change takes 15 seconds. How can I make these queries faster?

Answer

select in your code loads everything into memory which is very bad for performance when dealing with a lot of records.

You have to do the filtering in the database, with something like this.

UserBiz
  .includes(:business, :user)
  .where("LOWER(businesses.category) = LOWER(?)", params[:category])