Geokit-rails + MySQL: How to speed up search through latitude and longitude?

Question

I have a database table that contains columns storing latitude and longitude coords (both have the decimal data type). The table has about 500k rows through the searches are made.

The problem is that if I search rows that are in radius of 100-200 miles, the searches lasts about 140 seconds, which is not possible to put on production.

The stack:

1. Rails 4
2. MySQL 5.5
3. Geokit-rails gem

I'd like to ask you for help on how to speed up the searches through geographical data. Should I implement a search similar to kayak.com, where are displayed some results to users and the search is still running on the background?

Thank you in advance.

EDIT: 'stores' scheme

  create_table "stores", force: true do |t|
    t.string   "store_name"
    t.string   "address"
    t.string   "city"
    t.string   "state"
    t.string   "county"
    t.string   "zip_code"
    t.decimal  "latitude",                    precision: 10, scale: 6
    t.decimal  "longitude",                   precision: 10, scale: 6
  end

There are no indexes on the table. I've a similar table in the database that has latitude and longitude columns too, I tried to add indexes on them, but the loading time of the searches stayed the same.

Answer 1

If @spickermann answer won't work, IMO you should migrate to Postgres. PostGis is a very powerfull tool with great performance. http://postgis.net/

Answer 2

Best ways to solve this is using a geospatial index. However neither geokit nor geocoder do support them for relational databases. The rgeo gem does. It's however is not well documented. If you are doing some serios search stuff it probably will make sense to delegate this problem to elastic_search.

Answer 3

I would start with putting a combined index on the longitude and latitude columns like this:

class AddIndexToStoresLongitudeLatitude < ActiveRecord::Migration
  def up
    add_index :stores, [:latitude, :longitude]
  end

  def down
    remove_index :stores, [:latitude, :longitude]
  end
end