Index on multiple columns in Ruby on Rails

I'm implementing functionality to track which articles a user has read.

  create_table "article", :force => true do |t|
t.string   "title"
t.text     "content"
end

This is my migration so far:

create_table :user_views do |t|
t.integer :user_id
t.integer :article_id
end

The user_views table will always be queried to look for both columns, never only one. My question is how my index should look like. Is there a difference in the order of these tables, should there be some more options to it or whatever. My target DB is Postgres.

add_index(:user_views, [:article_id, :user_id])

Thanks.

UPDATE:
Because only one row containing the same values in both columns can exist (since in knowing if user_id HAS read article_id), should I consider the :unique option? If I'm not mistaken that means I don't have to do any checking on my own and simply make an insert every time a user visits an article.

76634

小开

最佳答案

The order does matter in indexing.

首先放置最有选择性的字段，也就是最快地缩小行数的字段。
The index will only be used insofar as you use its columns in sequence 从头开始. i.e. if you index on [:user_id, :article_id], you can perform a fast query on user_id or user_id AND article_id, but NOT on article_id.

您的迁移 add_index行应该是这样的:

add_index :user_views, [:user_id, :article_id]

关于“唯一”选项的问题

An easy way to do this in Rails is to use validates in your model with scoped uniqueness as follows (文件):

validates :user, uniqueness: { scope: :article }