CODEWITHSUNDEEP
mysqlruby-on-railspostgresqlindexing
saki7
saki7

Reputation: 682

How to specify a multiple column index correctly in Rails

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

Upvotes: 9

Views: 7470

Answers (2)

saki7
saki7

Reputation: 682

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.

Upvotes: 8

Anthony Alberto
Anthony Alberto

Reputation: 10395

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

Upvotes: 22

Related Questions