What does add_index with multiple values do in a Rails migration?

Question

I've read through this and looked at this, but I'm none-the-wiser:

What exactly does this do (as opposed to passing in a single column name) and what is the advantage?

add_index :resources, [:one, :two]

Answer 1

It adds a multi-column index on columns one and two in resources table.

The statement:

add_index :resources, [:one, :two], name: 'index_resources_one_two'

is equivalent to:

create index index_resources_one_two on resources(one, two)

Passing in a single column would only create index on that column only. For example the following line:

add_index :resources, :one, name: 'index_resources_one'

is equivalent to:

create index index_resources_one on resources(one)

The advantage of multi-column index is that it helps when you have a query with conditions on those multiple columns.

With multi-column index the query is worked on a smaller subset of data as compared to single column index when the query contains conditions on those multiple columns.

Say for example our resources table contains the following rows:

one, two 
 1,   1
 1,   1
 1,   3
 1,   4

The following query:

select * from resources where one = 1 and two = 1;

would only have to work on the following two rows if a multi-column index is defined:

one, two 
 1,   1
 1,   1

But, without the multi-column index, say for example there is an index on only one then the query would have to work on all the rows with one equal to 1 which is four rows.