What is a multi-key index?

Question

add_index :microposts, [:user_id, :created_at] 

I walked along the Michael Hartl railway and noticed that he was using something called an index with several keys. I know that this means that Active Record uses both keys at the same time, but I'm not quite sure about the advantages and disadvantages of using multiple key indexes.

If anyone can give an answer, I would really appreciate it.

Answer 1

Any index can give an advantage by allowing the query to narrow down the set of rows to check.

A multi-column index can help when your query includes conditions for these multiple columns.

For instance:

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

A multi-column index is narrowed down to a subset of the rows that are associated with user_id 123, and then inside that subset it further narrows down the selection to those with the latest created_at value.

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

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

Answer 2

In the general case, a search key can have several attributes that we referred to as a compiler key or several keys.

As an example, consider the university relations index. Here's the combined search key (course_id, year_of_enroll). Such an index would be useful for finding all students with a specific course identifier, and in particular, in the year when they entered the university. This index search process would pinpoint our desired record.