title: How to optimize select query with index
body: <p>I am not expert.
  I have following query, which contains 10 tables
  MainTable has 10 fields 1st Prime Key and rest foreign keys of 9 tables called TableE1 - 10.</p>
  
  <p>The following query is making outer join in each table, i want to optimize this query with index.</p>
  
  <p>I want to know, how can we optimize queries with index, this query is fetching 10 lacs (1 million) records in 36 seconds, how much time we can reduce ?</p>
  
  <p>MainTable contains 10 lacs (1 million) records, TableE1-9 each table contains 5000 records</p>
  
  <pre><code>select M.RecID, 
  M.E1, E1.Descr as E1_D, 
  M.E2, E2.Descr as E2_D, 
  M.E3, E3.Descr as E3_D, 
  M.E4, E4.Descr as E4_D, 
  M.E5, E5.Descr as E5_D, 
  M.E6, E6.Descr as E6_D, 
  M.E7, E7.Descr as E7_D, 
  M.E8, E8.Descr as E8_D, 
  M.E9, E9.Descr as E9_D
  from ((((((((tableMain M  
      Left Outer Join TableE1 E1 ON (E1.RecID = M.E1) )
      Left Outer Join TableE2 E2 ON (E2.RecID = M.E2) )
      Left Outer Join TableE3 E3 ON (E3.RecID = M.E3) )
      Left Outer Join TableE4 E4 ON (E4.RecID = M.E4) )
      Left Outer Join TableE5 E5 ON (E5.RecID = M.E5) )
      Left Outer Join TableE6 E6 ON (E6.RecID = M.E6) )
      Left Outer Join TableE7 E7 ON (E7.RecID = M.E7) )
      Left Outer Join TableE8 E8 ON (E8.RecID = M.E8) )
      Left Outer Join TableE9 E9 ON (E9.RecID = M.E9)
  Order by RecID
  </code></pre>