Rails extremely slow database querying
<p>My app currently has a table that has around 12.8 million rows in it and that table has a <code>has_one</code> association with another 5 tables where each of them has the same 12.8 million rows in them. </p>

<p>All of these data will then be displayed in a table with filtering capabilities using the <code>filterrific</code> gem. The problem is that it takes too much time to do queries like column specific searching whereas a <code>Table.count</code> query takes around 30-40 seconds to complete. Are there any query optimization methods that could greatly decrease the query time?</p>

<p>I have tried implementing eager load and selecting required columns to display only but the performance increase has not been significant.</p>

<p>Example schema:</p>

<p><strong>domain.rb</strong></p>

<pre><code>create_table "domains", force: :cascade do |t|
    t.string   "name",                                     null: false
    t.integer  "age",                      default: 0
    t.integer  "pr",                       default: 0
    t.boolean  "dmoz",                     default: false
    t.float    "price",                    default: 0.0
    t.string   "listing_type"
    t.datetime "created_at",                               null: false
    t.datetime "updated_at",                               null: false
    t.string   "source"
    t.datetime "end_date_time"
    t.integer  "no_bids",                  default: 0
    t.float    "traffic",                  default: 0.0
    t.float    "valuation",                default: 0.0
  end
add_index "domains", ["name"], name: "index_domains_on_name", unique: true, using: :btree
</code></pre>

<p>For the search filters, scopes are used and an example below:</p>

<pre><code>  scope :basic, lambda { |basic|
    query = ""
    query += "age BETWEEN #{check_min(basic.age_min.to_i)} AND #{check_max(basic.age_max.to_i)} " if basic.age_min.present? || basic.age_max.present?
    query += "AND price BETWEEN #{check_min(basic.price_min.to_f)} AND #{check_max(basic.price_max.to_f)} " if basic.price_min.present? || basic.price_max.present?
    query += "AND pr BETWEEN #{check_min(basic.pr_min.to_i)} AND #{check_max(basic.pr_max.to_i)} " if basic.pr_min.present? || basic.pr_max.present?
    if query[0..2] == 'AND'
      query = query[3..-1]
    end

    where(query)
  }
</code></pre>