使用子选择的MySQL查询选择花费的时间太长

I noticed something strange while executing a select from 2 tables:

我从2个表中执行select时发现了一些奇怪的东西:

SELECT * FROM table_1 WHERE id IN (
    SELECT id_element FROM table_2 WHERE column_2=3103);

This query took approximatively 242 seconds.

此查询大约需要242秒。

But when I executed the subquery

但是当我执行子查询时

SELECT id_element FROM table_2 WHERE column_2=3103

it took less than 0.002s (and resulted 2 rows).
Then, when I did

它花了不到0.002s(并产生了2行)。然后,当我做的时候

SELECT * FROM table_1 WHERE id IN (/* prev.result */)

it was the same: 0.002s.

它是相同的:0.002s。

I was wondering why MySQL is doing the first query like that, taking much more time than the last 2 queries separately? Is it an optimal solution for selecting something based from the results of a sub-query?

我想知道为什么MySQL会像这样做第一个查询,比最后两个查询分别花费更多的时间?它是根据子查询结果选择内容的最佳解决方案吗?

Other details: table_1 has approx. 9000 rows, and table_2 has 90000 rows.

其他细节:table_1有约。 9000行,table_2有90000行。

After I added an index on column_2 from table_2, the first query took 0.15s.

从table_2在column_2上添加索引后,第一个查询占用了0.15秒。

3 个解决方案

#1

Perhaps the query analyzer evaluates the subquery for every row.

查询分析器可能会评估每行的子查询。

Try replacing the subquery with an INNER JOIN, and see if that improves performance:

尝试使用INNER JOIN替换子查询,看看是否可以提高性能:

SELECT     * 
FROM       table_1 t1
INNER JOIN table_2 t2
ON         t1.id = t2.id_element
           AND t2.column_2 = 3103

#2

This is a known bug in mysql prior to ver 6.

这是ver 6之前的mysql中的已知错误。

Work around I have found is:

我找到的解决方法是:

SELECT * FROM table_1 WHERE id IN ( SELECT id_element FROM (SELECT id_element FROM table_2 WHERE column_2=3103) as q)

SELECT * FROM table_1 WHERE id IN(SELECT id_element FROM(SELECT id_element FROM table_2 WHERE column_2 = 3103)as q)

#3

I have the same problem. I added an INDEX for the tables (guess you already have) and used the USE INDEX directive. In your case it should look like this:

我也有同样的问题。我为表添加了一个INDEX(猜测你已经有)并使用了USE INDEX指令。在你的情况下,它应该是这样的:

SELECT * FROM table_1 USE INDEX(id)
WHERE id IN (SELECT id_element FROM table_2 WHERE column_2=3103);

To me it made things better.

对我而言,事情变得更好。

#1