title: How to properly index a many-many association table?
body: <p>In a typical many-many arrangement like this...</p>
  
  <pre>
  Movies       Actors       Movies_Actors
  ------       ------       -------------
  movie_ID     actor_ID     FK_movie_ID
  title        name         FK_actor_ID
  </pre>
  
  <p>... how should the association table (<code>'Movies_Actors'</code>) be indexed for optimal read speed?</p>
  
  <p>I usually see this done only with the composite primary key in the association table, like so:</p>
  
  <pre><code>CREATE TABLE Movies_Actors (
    FK_movie_ID INTEGER,
    FK_actor_ID INTEGER,
    PRIMARY KEY (FK_movie_ID, FK_actor_ID)
  )
  </code></pre>
  
  <p>However, this seems like the index will only be useful when searching for <em>both</em> <code>movie_ID</code> and <code>actor_ID</code> (although I'm not certain on whether a composite index also works for the individual columns).</p>
  
  <p>Since both "what actors are in Movie X" and "what movies has actor Y been in" will be the common queries for this table, it seems like there should be an individual index on each column to quickly locate actors and movies on their own.  Does a composite index effectively do this?  If not, having a composite index seems pointless on this table.  And if a composite index is pointless, what to do about a primary key?  The candidate key is clearly the composite of the two columns, but if the resulting composite index is pointless (it mustn't be?) it seems like a waste.</p>
  
  <p>Also, <a href="http://www.sqldev.org/getting-started-with-sql-server/best-practice-for-manytomany-relationship-102014.shtml" rel="noreferrer">this link</a> adds some confusion and indicates that it might even be useful to actually specify <em>two</em> composite indices... one of them as <code>(FK_movie_ID, FK_actor_ID)</code>, and the other in reverse as <code>(FK_actor_ID, FK_movie_ID)</code>, with the choice of which is the primary key (and thus usually clustered) and which is 'just' a unique composite index being based on which direction is queried more.</p>
  
  <p>What is the real story?  Does a composite index automatically effectively index each column for searching on one or the other?  Should the optimal (in read speed, not size) association table have a composite index in each direction <em>and</em> one on each column?  What are the behind-the-scene mechancs?</p>
  
  <hr>
  
  <p>EDIT: I found this related question that for some reason I didn't locate before posting...
  <a href="https://stackoverflow.com/questions/571309/how-to-properly-index-a-linking-table-for-many-to-many-connection-in-mysql">How to properly index a linking table for many-to-many connection in MySQL?</a></p>