1

I'm trying to speed up a complex rails query that basically searches for a location on a venue. The location can be ether a name, city, county or country.

I tried adding an index, however I don't think i have done this correctly.

This is my schema for venues:

  create_table "venues", force: :cascade do |t|
    t.string   "name"
    t.string   "address"
    t.string   "postcode"
    t.string   "city"
    t.string   "country"
    t.datetime "created_at",     null: false
    t.datetime "updated_at",     null: false
    t.string   "postplace"
    t.string   "map_address"
    t.decimal  "latitude"
    t.decimal  "longitude"
    t.string   "slug"
    t.text     "description"
    t.string   "website"
    t.integer  "import_id"
    t.string   "postal_address"
    t.string   "email"
    t.string   "county"
    t.string   "region"
    t.string   "country_name"
  end

  add_index "venues", ["country_name"], name: "index_venues_on_country_name", using: :btree
  add_index "venues", ["import_id"], name: "index_venues_on_import_id", using: :btree
  add_index "venues", ["name", "city", "county", "country"], name: "location", using: :btree
  add_index "venues", ["slug"], name: "index_venues_on_slug", unique: true, using: :btree

And this is the relevant part of my query:

  def filter_on_location
    @events = events.
      joins(:venue).where(
        "venues.city ILIKE :location
        OR venues.county ILIKE :location
        OR venues.country ILIKE :location
        OR venues.name = :location",
        location: params[:location])
  end

Any tips are appreciated!

max
  • 96,212
  • 14
  • 104
  • 165
Ole Henrik Skogstrøm
  • 6,353
  • 10
  • 57
  • 89
  • although it might be obvious to most, please add the database server you're using as tag – davejal Nov 28 '15 at 21:53
  • Added postgres tag. `ILIKE` is postgres specific AFAIK. – max Nov 29 '15 at 00:51
  • http://stackoverflow.com/questions/12126615/postgresql-ilike-versus-tsearch – max Nov 29 '15 at 00:55
  • There is no good way for this query to avoid a table scan while you're using B-tree indexes. Search for details on Postgres full-text search and you have a chance. – cliffordheath Nov 29 '15 at 02:03

0 Answers0