title: What indexes should be used when the query results are ordered by a has_many :through association
body: <p>The question may be long but the problem is actually simple. I have 3 models: List, Appearance and Movie. A List has many Movies through an Appearance, and they should be sorted by the attribute rank of the joins model. So what indexes should I be using? This is how my models currently look:</p>
  
  <pre><code># Models
  class Movie &lt; ActiveRecord::Base  
    has_many :appearances, :dependent =&gt; :destroy
    has_many :lists, :through =&gt; :appearances
  end
  
  class Appearance &lt; ActiveRecord::Base
    belongs_to :list
    belongs_to :movie
  end
  
  class List &lt; ActiveRecord::Base
    has_many :appearances, :dependent =&gt; :destroy
    has_many :movies, :through =&gt; :appearances
  
    def self.find_complete(id)
      List.includes({:appearances =&gt; :movie}).where("appearances.rank IS NOT NULL").order("appearances.rank ASC").find(id)
    end
  end
  </code></pre>
  
  <p>And here are the indexes I already have. Do I need a composite index? Or just an index by rank?</p>
  
  <pre><code># Tables
  class CreateAppearances &lt; ActiveRecord::Migration
    def change
      create_table :appearances do |t|
        t.integer :list_id, :null =&gt; false
        t.integer :movie_id, :null =&gt; false
        t.integer :rank
      end
      add_index :appearances, :list_id
      add_index :appearances, :movie_id
    end
  end
  </code></pre>
  
  <p>Finally, is there any way to refactor that find_complete List method? Like using a default_scope? Don't forget that a rank may be null.</p>