How to properly index fields on a Rails join table migration?

Question

Rails 4 introduced a feature for generating join table migrations:

bin/rails generate migration CreateTeamsUsersJoinTable team user

This results in the following file:

class CreateTeamsUsersJoinTable < ActiveRecord::Migration
  def change
    create_join_table :teams, :users do |t|
      # t.index [:team_id, :user_id]
      # t.index [:user_id, :team_id]
    end
  end
end

You can see that the indices are commented out. Why is this? Am I supposed to make a decision about which one I want to use? Or are they implying that indices are optional? I want to make sure there is some sort of index on each field to avoid performance problems, but I'm not sure how to properly define the indices for these fields.

Answer 1

To complete xlembouras answer, you make your choice based on how you query your table.

For example, if you have a view that shows the users of a given team, the query will look like this :

SELECT user.name FROM team_users tu 
INNER JOIN users u 
ON tu.user_id = u.id
WHERE tu.team_id = X

This benefits from t.index [:team_id, :user_id] but not t.index [:user_id, :team_id]

The database will only use one index seek on the first index to retrieve the user ids

If you have a view that shows the teams of a given user, the query would be something like the following :

SELECT team.name FROM team_users tu 
INNER JOIN team t 
ON tu.team_id = t.id
WHERE u.user_id = X

This benefits from t.index [:user_id, :team_id] but not t.index [:team_id, :user_id]

The database will only use one index seek on the first index to retrieve the team ids

That's because of the way composed index are stored :

Answer 2

I would have to say that the intention behind this is to let you choose the strategy that fits you better. That is usually a decision with two criteria

• how much overhead does indexing adds
• how you'll query your db

So they can not make that decision for you and add the indexing overhead as a convention.

In order to make that decision you need to know how you intend to query your database. In MySQL composite indexes can be of use for a set of queries. multiple-column index

An index on column_a, column_b, column_c will be used for queries on

• all three fields together
• column_a alone
• column_a and column_b together

So there is a difference between

t.index [:team_id, :user_id]

and

t.index [:user_id, :team_id]

the actual full set of indexes that you 'll need is

t.index [:team_id, :user_id]
t.index :user_id

or

t.index [:user_id, :team_id]
t.index :team_id

In order to handle with an index all three cases.

example

if you find your app to use more frequently queries like

user.teams which translate to

select * from teams where user_id = X

an index on user_id (or user_id, team_id) can be handy

so t.index [:user_id, :team_id] should be your choice