Which query will run faster in MySql

Question

I created indexing in my tables, and then I run the same queries using two different methods: I run these queries on MySql, but always get a different runtime, sometimes faster at first, and sometimes the second. That's why I want expert opinion on this .Queries is First -

  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 someone tell me what will give me high performance and why, since my database is too big ... So I wanted to know which one is better to write such queries in MySql.

Answer 1

the second approach will be faster than the first,

t1 (), t2 (), t3 ()

, , mysql

, mysql,

Answer 2

, , , , , .., .

MySQL EXPLAIN.

Answer 3

EXPLAIN, , .

Subqueries in the WHERE clause are best avoided since they should potentially be run for each row. When a subquery is required, it is best used in the FROM clause, creating a view . For instance.

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 4

Use join and create an index for those columns that are involved in the comparison. Then use "Explain" to check the performance :)