CODEWITHSUNDEEP
ruby-on-railsdatabase-designindexing
daremkd
daremkd

Reputation: 8424

Multi-column index order in Rails

I understand why the index order matters in Rails (from answers like these), for example, if I have:

add_index :admin_users_pages, ["user_id", "page_id"]

So I'm supposed to put the field that "narrows down the number of rows" fastest, but I'm not sure what does that mean. Say I have 2 users, with 2 unique IDs, and 300 pages, with 300 unique IDs, which one would be a smarter choice to put first? Say I have 150 pages for the first user and 150 pages for the second user, would the index look something like:

user_id page_id
1       1
1       2  
1       3

or the page_id won't be sorted at all, only the index so I should get something like:

user_id page_id
1       143
1       93  
1       31

Upvotes: 5

Views: 1684

Answers (2)

David Aldridge
David Aldridge

Reputation: 52386

If for a given user you want to find its pages, use [:user_id, :page_id].

If for a given page you want to find its users, use [:page_id, :user_id].

If you want to do both, then create [:user_id, :page_id] and [:page_id, :user_id].

If you have a user_id and a page_id and you want to find that row (not a very likely situation, IMHO), then for a balanced tree index it doesn't matter which order you have chosen. The entries are sorted within the index for both the first and the second and subsequent columns.

In some situations it is arguable that the least selective should go first (for Oracle compressed b-tree indexes or for Oracle skip-scan access), but in general it really doesn't matter.

Upvotes: 3

lx00st
lx00st

Reputation: 1596

In your case the selectivity of page_id will be better, because it narrows down number of rows extremely fast (down to 2). It means that if you are given a page_id than you can take 2 records from from table and then filter them by user_id, but if you have user_id then you will take 150 records and filter them. So it is better to put 'page_id' first.

Upvotes: 1

Related Questions