How to specify a multiple column index correctly in Rails

Question

As shown in this question: How do I make a column unique and index it in a Ruby on Rails migration?
You can write:

add_index :the_table, [:foo_column, :bar_column], unique: true

to add an multiple column index.
But is it still required to add an single indexes for each of those columns that you already have specified a multi-column index?

I mean something like writing below code in additional to the code shown above.

add_index :the_table, :foo_column  
add_index :the_table, :bar_column

Anthony Alberto · Accepted Answer · 2012-11-08T23:50:50.593

22

For MySQL :

MySQL will be able to use the index [:foo_column, :bar_column] to query for conditions on both columns, and also for conditions on the left column only, but NOT the right column.

More info here : http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

So you should do

add_index :the_table, [:foo_column, :bar_column], :unique => true
add_index :the_table, :bar_column

To make sure you index everything properly

MySQL indexes columns left-to-right so if you have a multi-column index like this : [:col1, :col2, :col3, :col4], you can query this index on :

col1
col1 + col2
col1 + col2 + col3
col1 + col2 + col3 + col4

So you can query the left-most columns

If you need anything else, you'll have to create more indexes

Again, that's only for MySQL, postgres may work differently

edited Nov 08 '12 at 23:50

answered Nov 08 '12 at 22:07

Anthony Alberto

10,325
3
34
38

Wow, I really didn't imagine this answer. It seems like a quite odd behavior for me. Since the documentation says that MySQL checks columns from left-to-right order, is that mean you need to add indexes for every column except for the first one (when specifying more than 2 columns in multi-column index)? – saki7 Nov 08 '12 at 22:09
Ah... never mind. It looks like it behaves like I just commented. The doc says "it also acts like an index for the **left-most** column". – saki7 Nov 08 '12 at 22:18
Edited my answer with more info, it's the left-most columns <= plural! – Anthony Alberto Nov 08 '12 at 23:07
1

I see! Now I understand what 'left-most columns' mean. English is difficult. – saki7 Nov 08 '12 at 23:49
1

wish I have 8 hands, so I could give this answer 8 thumbs up ..thx – equivalent8 Oct 16 '13 at 16:07
What about NULL columns? – ocodo Jul 15 '15 at 06:46
Nothing special regarding NULL columns ... you can specify IS NULL or IS NOT NULL instead of a value for each column – Anthony Alberto Mar 18 '16 at 18:53

score 7 · Answer 2 · edited Nov 09 '12 at 00:39

For PostgreSQL:

PostgreSQL: Documentation: Multicolumn Indexes says:

A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned.

It seems like PostgreSQL behaves the same as MySQL in this area.

How to specify a multiple column index correctly in Rails

2 Answers2