title: Best SQL indexes for join table
body: <p>With performance improvements in mind, I was wondering if and which indexes are helpful on a join table (specifically used in a Rails 3 has_and_belongs_to_many context).</p>
  
  <h2>Model and Table Setup</h2>
  
  <p>My models are <code>Foo</code> and <code>Bar</code> and per rails convention, I have a join table called <code>bars_foos</code>. There is no primary key or timestamps making the old fields in this table <code>bar_id:integer</code> and <code>foo_id:integer</code>. I'm interested in knowing which of the following indexes is best and is without duplication:</p>
  
  <ol>
  <li>A compound index: <code>add_index :bars_foos, [:bar_id, :foo_id]</code></li>
  <li><ul>
  <li>Two indexes</li>
  <li>A. <code>add_index :bars_foos, :bar_id</code></li>
  <li>B. <code>add_index :bars_foos, :foo_id</code></li>
  </ul></li>
  <li>A combination of both 1 and 2-B</li>
  </ol>
  
  <p>Basically, I'm not sure if the compound index is enough assuming it is helpful to begin with. I believe that a compound index can be used as a single index for the first item which is why I am <em>pretty</em> sure that using all three lines would certainly result in unnecessary duplication.</p>
  
  <h2>Likely Usage</h2>
  
  <p>The most common usage will be given an instance of model <code>Foo</code>, I will be asking for its associated <code>bars</code> using the RoR syntax of <code>foo.bars</code> and vice versa with <code>bar.foos</code> for an instance of the model <code>Bar</code>.</p>
  
  <p>These will generate queries of the type <code>SELECT * FROM bars_foos WHERE foo_id = ?</code> and <code>SELECT * FROM bars_foos WHERE bar_id = ?</code> respectively and then using those resultant IDs to <code>SELECT * FROM bars WHERE ID in (?)</code> and <code>SELECT * FROM foos WHERE ID in (?)</code>.</p>
  
  <p>Please correct me in the comments if I am incorrect, but I do not believe that, in the context of the Rails application, it is ever going to try to do a query where it specifies both IDs like <code>SELECT * FROM bars_foos where bar_id = ? AND foo_id = ?</code>.</p>
  
  <h2>Databases</h2>
  
  <p>In the event there are database specific optimization techniques, I will most likely be using PostgreSQL. However, others using this code may want to use it in MySQL or SQLite depending on their Rails configuration so all answers are appreciated.</p>