How to properly index fields in a Rails connection table migration?

Question

Rails 4 introduced a feature for creating connection table migration:

bin/rails generate migration CreateTeamsUsersJoinTable team user 

The result is 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 the indexes are commented out. Why is this? Do I have to decide which one I want to use? Or do they imply that indices are optional? I want to make sure there is some kind of index for each field in order to avoid performance issues, but I'm not sure how to correctly determine the indexes for these fields.

Answer 1

To fulfill the xlembouras answer, you make your choice depending on how you query your table.

For example, if you have a view showing users of this command, 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 gives the advantages of t.index [:team_id, :user_id] , but not t.index [:user_id, :team_id]

The database will use only one index search on the first index to retrieve user identifiers

If you have a view showing the commands of this user, the query will look something like this:

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

It depends on t.index [:user_id, :team_id] , but not t.index [:team_id, :user_id]

The database will use only one index for the first index to retrieve team identifiers

This is because the index compilation method is stored:

Answer 2

I had to say that the intention is to let you choose a strategy that suits you. This is usually a solution with two criteria

• how much overhead indexes adds
• how do you request your db

Therefore, they cannot make this decision for you and add indexing overhead as an agreement.

To make this decision, you need to know how you are going to query your database. In MySQL, composite indexes can be useful for a set of queries. multi-column pointer

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

• all three fields together
• column_a
• 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 complete set of indexes you will need

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

or

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

To handle all three cases with an index.

Example

if you find that your application uses queries such as

user.teams that translate to

select * from teams where user_id = X

index user_id (or user_id, team_id ) can be convenient

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