CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresql
Mateusz Urbański
Mateusz Urbański

Reputation: 7862

Optimize query to make Order by random run faster

In my rails app I have a method that looks like this:

def find_person(field, attribute)
  Person.where.not(Hash[field, nil])
      .where.not("lower(#{field})  = ?", attribute.downcase)
      .where("difference(#{field}, ?) < 2", attribute)
      .order('RANDOM()')
      .limit(3)
      .pluck("lower(#{field})")
      .uniq
end

This query is very slow because of .order('RANDOM()'). How can I make this query faster?

Upvotes: 3

Views: 126

Answers (1)

Mark Swardstrom
Mark Swardstrom

Reputation: 18080

Another approach, use Ruby to get the 3 random records.

def find_person(field, attribute)
  ids = Person.where.not(Hash[field, nil])
      .where.not("lower(#{field})  = ?", attribute.downcase)
      .where("difference(#{field}, ?) < 2", attribute)
      .pluck("id")

  Person.where(:id => ids.sample(3))
end

If pluck is too slow, another approach.

def find_person(field, attribute)
  q = Person.where.not(Hash[field, nil])
    .where.not("lower(#{field})  = ?", attribute.downcase)
    .where("difference(#{field}, ?) < 2", attribute)

  max = q.count
  offsets = [rand(max), rand(max), rand(max)]
  offsets.map { |o| q.offset(o).limit(1).pluck("lower(#{field})").first }

end

Upvotes: 1

Related Questions