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

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