title: How can I improve this one to many ActiveRecord data model?
body: <p>I have a home-grown (not my own) versioning system with the following data structure:</p>
  
  <pre><code>  create_table "activities", :force =&gt; true do |t|
      t.string   "source"
      t.datetime "created_at",       :null =&gt; false
      t.datetime "updated_at",       :null =&gt; false
      t.integer  "head_revision_id"
    end
  
    add_index "activities", ["head_revision_id"], :name =&gt; "index_activities_on_head_revision_id"
    add_index "activities", ["source"], :name =&gt; "index_activities_on_source"
  
    create_table "activity_revisions", :force =&gt; true do |t|
      t.integer  "activity_id"
      t.string   "activity_type"
      t.string   "title"
      t.text     "content"
      t.text     "comment"
      t.integer  "modified_by_id"
      t.datetime "created_at",                      :null =&gt; false
      t.datetime "updated_at",                      :null =&gt; false
    end
  
    add_index "activity_revisions", ["activity_id"], :name =&gt; "index_activity_revisions_on_activity_id"
    add_index "activity_revisions", ["activity_type"], :name =&gt; "index_activity_revisions_on_activity_type"
    add_index "activity_revisions", ["title"], :name =&gt; "index_activity_revisions_on_title"
  </code></pre>
  
  <p>The application displays a list of activities from newest to oldest, paginated (will_paginate) 20 to a page. This is the query used to generate the list:</p>
  
  <pre><code>Activity.where(conditions)
          .joins(:head_revision)
          .includes(:head_revision)
          .order('activities.id DESC')
  </code></pre>
  
  <p>The <code>conditions</code> vary according the values passed from a search form. For the initial list display, <code>conditions</code> is blank.</p>
  
  <p>On the surface, this query is simple enough but in execution, it is horribly slow with large data sets. We currently have about 102,000 activity records and 512,000 activity_revision records. On our production server, the query takes nearly 2 seconds to provide a count. In a development environment, it is abysmal.</p>
  
  <p>I feel that there is something inherently wrong with the data model and I'm hoping someone can show me a better way.</p>
  
  <p>EDIT: Explain run on the basic query without conditions:</p>
  
  <pre><code>mysql&gt; explain SELECT * FROM `activities`  INNER JOIN `activity_revisions` ON `activity_revisions`.`id` = `activities`.`head_revision_id`;
  +----+-------------+--------------------+--------+--------------------------------------+---------+---------+--------------------------------------------+--------+-------+
  | id | select_type | table              | type   | possible_keys                        | key     | key_len | ref                                        | rows   | Extra |
  +----+-------------+--------------------+--------+--------------------------------------+---------+---------+--------------------------------------------+--------+-------+
  |  1 | SIMPLE      | activities         | ALL    | index_activities_on_head_revision_id | NULL    | NULL    | NULL                                       | 106590 |       |
  |  1 | SIMPLE      | activity_revisions | eq_ref | PRIMARY                              | PRIMARY | 4       | cms_production.activities.head_revision_id |      1 |       |
  +----+-------------+--------------------+--------+--------------------------------------+---------+---------+--------------------------------------------+--------+-------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>and on the count(*) query:</p>
  
  <pre><code>mysql&gt; explain SELECT count(*) FROM `activities`  INNER JOIN `activity_revisions` ON `activity_revisions`.`id` = `activities`.`head_revision_id`;
  +----+-------------+--------------------+--------+--------------------------------------+--------------------------------------+---------+--------------------------------------------+--------+-------------     +
  | id | select_type | table              | type   | possible_keys                        | key                                  | key_len | ref                                        | rows   | Extra            |
  +----+-------------+--------------------+--------+--------------------------------------+--------------------------------------+---------+--------------------------------------------+--------+-------------     +
  |  1 | SIMPLE      | activities         | index  | index_activities_on_head_revision_id | index_activities_on_head_revision_id | 5       | NULL                                       | 106590 | Using index      |
  |  1 | SIMPLE      | activity_revisions | eq_ref | PRIMARY                              | PRIMARY                              | 4       | cms_production.activities.head_revision_id |      1 | Using index      |
  +----+-------------+--------------------+--------+--------------------------------------+--------------------------------------+---------+--------------------------------------------+--------+-------------     +
  2 rows in set (0.00 sec)
  </code></pre>