Indexes for has_many :through

Question

Suppose you have two models, User and City, joined by a third model CityPermission:

class CityPermission < ActiveRecord::Base
  belongs_to :city
  belongs_to :user
end

class City < ActiveRecord::Base
  has_many :city_permissions
  has_many :users, :through => :city_permissions
end

class User < ActiveRecord::Base
  has_many :city_permissions
  has_many :cities, :through => :city_permissions
end

Currently, I create the join table, and the index for the table, using the following migration code snippet:

create_table :city_permissions do |t|
      t.integer :user_id, :city_id
      t.other_fields ...
end

add_index(:city_permissions, :user_id)
add_index(:city_permissions, :city_id)

Are these the optimal indexes to create? Will these indexes allow quick access back and forth through the join table, as well as quick lookups within the table itself, or is there some other better way? To restate this a bit differently, will these indexes, given city and user are instance variables of class City and User, allow city.users, city.city_permissions, user.cities, and user.city_permissions to all perform equally well?

Answer 1

Looks good to me.

The joins generated should just be on either the PK IDs of the entity tables, or on the FK IDs in the join table - which are both indexes.

Probably would be good to look at the generated ActiveRecord SQL and compare it against the indexes.

Depending on what database you're on you could then run that SQL through an Explain plan (or whatever tool exists, I'm thinking Oracle here)

To simplify your code, you could look at using has_and_belongs_to_many as well. That would let you get rid of the CityPermission object (unless you want to use that to store data in itself)

Answer 2

Here is the SQL that ActiveRecord generates for user.cities:

SELECT `cities`.* FROM `cities` INNER JOIN city_permissions ON (cities.id = city_permissions.city_id) WHERE (city_permissions.user_id = 1 )

EXPLAIN results below:

+----+-------------+------------------+--------+---------------------------------------------------------------------+-----------------------------------+---------+-------------------------------------------------+------+-------------+
| id | select_type | table            | type   | possible_keys                                                       | key                               | key_len | ref                                             | rows | Extra       |
+----+-------------+------------------+--------+---------------------------------------------------------------------+-----------------------------------+---------+-------------------------------------------------+------+-------------+
|  1 | SIMPLE      | city_permissions | ref    | index_city_permissions_on_user_id,index_city_permissions_on_city_id | index_city_permissions_on_user_id | 5       | const                                           |    1 | Using where |
|  1 | SIMPLE      | cities           | eq_ref | PRIMARY                                                             | PRIMARY                           | 4       | barhopolis_development.city_permissions.city_id |    1 |             |
+----+-------------+------------------+--------+---------------------------------------------------------------------+-----------------------------------+---------+-------------------------------------------------+------+-------------+

And here's the SQL that ActiveRecord generates for user.city_permissions:

SELECT * FROM `city_permissions` WHERE (`city_permissions`.user_id = 1)

With the EXPLAIN results for that query:

+----+-------------+------------------+------+-----------------------------------+-----------------------------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys                     | key                               | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+-----------------------------------+-----------------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | city_permissions | ref  | index_city_permissions_on_user_id | index_city_permissions_on_user_id | 5       | const |    1 | Using where |
+----+-------------+------------------+------+-----------------------------------+-----------------------------------+---------+-------+------+-------------+

Looks like it is indeed working correctly. From the MySQL Manual:

eq_ref

One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE index.

ref

All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.