CODEWITHSUNDEEP
ruby-on-railsrails-activerecorddatabase-migrationrails-migrations
Kamil Lelonek
Kamil Lelonek

Reputation: 14744

Rails migrations indexing

What is the difference between these two methods of adding indexes:

add_index :juice_ingredients, %i(juice_id ingredient_id)

and:

add_index :juice_ingredients, :juice_id
add_index :juice_ingredients, :ingredient_id

Moreover, do I need to explicitly create join table or just add_reference is enough?

Upvotes: 0

Views: 99

Answers (2)

Simone Carletti
Simone Carletti

Reputation: 176382

The difference is that the first statement creates a multi-column index (also called composite index), the second creates two single-column indexes.

Both versions results in the columns :juice_id and :ingredient_id being indexed at database level. However, they behave a little bit differently.

In order to better understand how, you need to have some knowledge of what is a database index and what you can use it for.

Composite indexes are recommended if you are likely to query the database using both columns in the same query. Moreover, you can use an index with an unique constraint to make sure that no duplicate records are created for a specific key, or combination of key.

Here's some additional articles:

Upvotes: 0

Philip Hallstrom
Philip Hallstrom

Reputation: 19879

The first will create a single index on two columns. The second will create two indexes, each on their own column. Which is better depends on your application, the database, and the queries you run. To figure out which you need to read up on "query optimization" for your database.

Upvotes: 1

Related Questions