mysql - How can I improve this one to many ActiveRecord data model? -

i have home-grown (not own) versioning system following data structure:

  create_table "activities", :force => true |t|     t.string   "source"     t.datetime "created_at",       :null => false     t.datetime "updated_at",       :null => false     t.integer  "head_revision_id"   end    add_index "activities", ["head_revision_id"], :name => "index_activities_on_head_revision_id"   add_index "activities", ["source"], :name => "index_activities_on_source"    create_table "activity_revisions", :force => true |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 => false     t.datetime "updated_at",                      :null => false   end    add_index "activity_revisions", ["activity_id"], :name => "index_activity_revisions_on_activity_id"   add_index "activity_revisions", ["activity_type"], :name => "index_activity_revisions_on_activity_type"   add_index "activity_revisions", ["title"], :name => "index_activity_revisions_on_title" 

the application displays list of activities newest oldest, paginated (will_paginate) 20 page. query used generate list:

activity.where(conditions)         .joins(:head_revision)         .includes(:head_revision)         .order('activities.id desc') 

the conditions vary according values passed search form. initial list display, conditions blank.

on surface, query simple enough in execution, horribly slow large data sets. have 102,000 activity records , 512,000 activity_revision records. on our production server, query takes 2 seconds provide count. in development environment, abysmal.

i feel there inherently wrong data model , i'm hoping can show me better way.

edit: explain run on basic query without conditions:

mysql> explain select * `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   | | +----+-------------+--------------------+--------+--------------------------------------+---------+---------+--------------------------------------------+--------+-------+ |  1 | simple      | activities         |    | 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) 

and on count(*) query:

mysql> explain select count(*) `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   |            | +----+-------------+--------------------+--------+--------------------------------------+--------------------------------------+---------+--------------------------------------------+--------+-------------     + |  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) 

i see indexing several columns good. 1 of best ways can ensure queries efficient possible make sure all of conditions deal querying/retrieval in db, have corresponding columns indexed.