哪个查询将在MySql中执行得更快

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

我在我的表上创建了索引,然后使用两种不同的方式触发相同的查询:我在MySql上运行这些查询,但总是得到不同的执行时间,有时候第一次更快,有时是第二次......这就是为什么我想要专家对此的意见。查询是首先是

  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

第二次使用join作为

 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.

所以任何人都可以告诉我哪些会给我带来高性能以及为什么我的数据库太大了....所以我想知道在MySql中编写此类查询的更好方法。

4 个解决方案

#1

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)

正确的索引将在t1(字段),t2(字段),t3(字段)上具有索引

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

当您使用explain作为第一种方法时,您将看到mysql将为这两个子查询创建两个派生表

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

而在第二种方法中,mysql检查的行将要少得多

#2

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.

看一下MySQL EXPLAIN语句。

#3

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

使用EXPLAIN作为查询的前缀,以查看它们的分析方式。

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.

最好避免使用WHERE子句中的子查询,因为它们可能需要为每一行执行。当需要子查询时,最好在FROM子句中使用它,从而创建派生表。例如。

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

#4

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

在参与比较的列上使用join和create index。然后使用“解释”来检查性能:)

#1