Which query will execute faster in MySql

Question

Hi folks, I created indexing on my tables and then I fire same queries using two different ways:I run those queries on MySql but always got different execution time sometimes first one is faster and sometimes second..Thats why I want experts opinion on this.Queries are First one is

  select t1.field 
  from table1 as t1 
  where t1.field in (
      select t2.field 
      from table2 as t2 
      where t2.field in (
        select t3.field from table3 as t3 
          where t3.field='something'))

And Second using join as

 select t1.field 
 from table1 as t1, 
      table2 as t2,
      table3 as t3 
 where t1.field = t2.field 
  and t2.field = t3.field 
  and t3.field='something'

So can anyone one tell me which will give me high performance and why as my DB is too big....So I wanted to know which is the better way to write such queries in MySql.

Answer 1

Only you can really answer your question, because only you have access to the exact combination of hardware, software, schema, indexes, data etc that your queries will actually be running against.

Take a look at the MySQL EXPLAIN statement.

Answer 2

Prefix the queries with EXPLAIN to see how they are analysed.

Subqueries in the WHERE clause are best avoided, as they potentially need to be executed for every row. When a subquery is required, it's best used in the FROM clause, creating a derived table. E.g.

SELECT *
FROM
  some_table
  INNER JOIN (
    SELECT * FROM another_table
  ) derived_table ON some_table.id = derived_table.id
WHERE
  some_table.x = 'y';

Answer 3

the second approach is going to be faster than the first one,.

proper indexing would be having indexes on t1(field), t2(field), t3(field)

when you use explain for the first approach you will see that mysql will create two derived tables for both the two subqueries

while in the second approach the rows examined by mysql are going to be far less

Answer 4

Use join and create index on those columns which participate in comparison. Then use "Explain" to check performance :)