Optimal query writing

Question

I have 3 tables t1,t2,t3 each having 35K records.

select t1.col1,t2.col2,t3.col3
  from table1 t1,table2 t2,table3 t3
 where t1.col1 = t2.col1
   and t1.col1 = 100  
   and t3.col3 = t2.col3 
   and t3.col4 = 101 
   and t1.col2 = 102;

It takes more time to return the result (15 secs). I have proper indexes.

What is the optimal way of rewriting it?

Answer 1

It's probably best to run your query with Explain Extended placed in front of it. That will give you a good idea of what indexes it is or isn't using. Include the output in your question if you need help parsing the results.

Answer 2

If you have an index based on t1.Col1 or t1.Col2, use THAT as the first part of your WHERE clause. Then, by using the "STRAIGHT_JOIN" clause, it tells MySQL to do exactly as I've listed here. Yes, this is older ANSI querying syntax which is still completely valid (as you originally had too), but should come out quickly with a response. The first two of the where clause will immediately restrict the dataset while the rest actually completes the joins to the other tables...

select STRAIGHT_JOIN
      t1.Col1,
      t2.Col2,
      t3.Col3
   from
      table1 t1,
      table2 t2,
      table3 t3
   where 
          t1.Col1 = 100
      and t1.Col2 = 102
      and t1.col1 = t2.col1
      and t2.col3 = t3.col3
      and t3.Col4 = 101