What is a multiple key index?

Question

add_index :microposts, [:user_id, :created_at]

I was going through Michael Hartl's railstutorial and noticed he's using something called a multiple key index. I know it means Active Record uses both keys at the same time, but I'm not really sure what the advantages and disadvantages of using multiple key indexes are.

If anyone can give an answer I would greatly appreciate it.

Answer 1

Any index can give benefit by allowing a query to narrow down the set of rows to examine.

A multi-column index can help when your query includes conditions on those multiple columns.

For example:

SELECT * FROM Mytable WHERE user_id = 123 AND created_at > '2013-02-01'

The multi-column index narrows down to the subset of rows that are associated with user_id 123, then within that subset, it further narrows down the selection to those with a recent created_at value.

Without the second column in the index, the RDBMS would have to load all the rows for user_id 123 into memory before it could determine if they pass the criteria.

For more information, see my presentation How to Design Indexes, Really.