CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3database-designindexing
Emil Ahlbäck
Emil Ahlbäck

Reputation: 6206

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.

Upvotes: 130

Views: 89078

Answers (2)

sscirrus
sscirrus

Reputation: 56789

The order does matter in indexing.

  1. Put the most selective field first, i.e. the field that narrows down the number of rows fastest.
  2. The index will only be used insofar as you use its columns in sequence starting at the beginning. 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.

Your migration add_index line should look something like this: 

add_index :user_views, [:user_id, :article_id]

Question regarding 'unique' option

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

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

Upvotes: 273

olivier
olivier

Reputation: 371

Just a warning about checking uniqueness at validation time vs. on index: the latter is done by database while the primer is done by the model. Since there might be several concurrent instances of a model running at the same time, the validation is subject to race conditions, which means it might fail to detect duplicates in some cases (eg. submit twice the same form at the exact same time).

Upvotes: 37

Related Questions