How should I use Rails to index and query a join table?

Question

I have a ruby on Rails 4 app, using devise and with a User model and a Deal model.

I am creating a user_deals table for has_many/has_many relationship between User and Deal.

Here is the migration

class CreateUserDeals < ActiveRecord::Migration

  def change
    create_table :user_deals do |t|
        t.belongs_to :user
      t.belongs_to :deal
      t.integer         :nb_views

      t.timestamps
    end
  end
end

When a user load a Deal (for example Deal id= 4), I use a method called show

controllers/deal.rb
#for the view of the Deal page
def show
end

In the view of this Deal id=4 page, I need to display the nb of views of the Devise's current_user inside the Deal page the user is currently on.

deal/show.html

here is the nb of views of user: <% current_user.#{deal_id}.nb_views%>

Lets' say I have 10M+ user_deals lines, I wanted to know if I should use an index

add_index :user_deals, :user_id
add_index :user_deals, :deal_id

or maybe

add_index(:deals, [:user_id, deal_id])

Indeed in other situations I would have said Yes, but here I don't know how Rails works behind the scenes. It feels as if Rails is aware of what to do without me needing to speed up the process,...as if when Rails loads this view that there is no SQL query (such as 'find the nb of views WHERe user_id= x and deal_id= Y')....because I'm using just for the current_user who is logged-in (via devise's current_user) and for deal_id Rails knows it as we are on the very page of this deal (show page) so I just pass it as a parameter.

So do I need an index to speed it up or not?

Answer 1

Your question on indexes is a good one. Rails does generate SQL* to do its magic so the normal rules for optimising databases apply.

The magic of devise only extends to the current_user. It fetches their details with a SQL query which is efficient because the user table created by devise has helpful indexes on it by default. But these aren't the indexes you'll need.

Firstly, there's a neater more idiomatic way to do what you're after

class CreateUserDeals < ActiveRecord::Migration
  def change
    create_join_table :users, :deals do |t|
      t.integer :nb_views
      t.index [:user_id, :deal_id]
      t.index [:deal_id, :user_id]
      t.timestamps
    end
  end
end

You'll notice that migration included two indexes. If you never expect to create a view of all users for a given deal then you won't need the second of those indexes. However, as @chiptuned says indexing each foreign key is nearly always the right call. An index on an integer costs few write resources but pays out big savings on read. It's a very low cost default defensive position to take.

You'll have a better time and things will feel clearer if you put your data fetching logic in the controller. Also, you're showing a deal so it will feel right to make that rather than current_user the centre of your data fetch.

You can actually do this query without using the through association because you can do it without touching the users table. (You'll likely want that through association for other circumstances though.) Just has_many :user_deals will do the job for this.

To best take advantage of the database engine and do this in one query your controller can look like this:

def show
  @deal = Deal.includes(:user_deals)
              .joins(:user_deals)
              .where("user_deals.user_id = ?", current_user.id)
              .find(params["deal_id"])
end

Then in your view...

I can get info about the deal: <%= @deal.description %>

And thanks to the includes I can get user nb_views without a separate SQL query: <%= @deal.user_deals.nb_views %>

* If you want to see what SQL rails is magically generating just put .to_sql on the end. e.g. sql_string = current_user.deals.to_sql or @deal.to_sql

Answer 2

Yes, you should use an index to speed up the querying of the user_deals records. Definitely at least on user_id, but probably both [:user_id, :deal_id] as you stated.

As for why you don't see a SQL query...

First off, your code in the view appears to be incorrect. Assuming you have set up a has_many :deals, through: :user_deals association on your User class, it should be something like:

here is the nb of views of user: <%= current_user.deals.find(deal_id).nb_views %>

If you see the right number showing up for nb_views, then a query should be made when the view is rendered unless current_user.deals is already being loaded earlier in the processing or you've got some kind of caching going on.

If Rails is "aware", there is some kind of reason behind it which you should figure out. Expected base Rails behavior is to have a SQL query issued there.

Answer 3

Is a cleaner way of indexing other tables not:

class CreateUserDeals < ActiveRecord::Migration

   def change
      create_table :user_deals do |t|
         t.references :user
         t.references :deal
         t.integer :nb_views

         t.timestamps
      end
   end
end