为什么MySQL不使用此子查询的索引？

I used to do this:

我曾经这样做过:

SELECT layerID
FROM layers
WHERE ownerID = ?
AND collectionID = ?

Which would give me an array of layerID's, and then I'd loop and do this for each one:

哪个会给我一个layerID的数组,然后我循环并为每个执行此操作:

SELECT DATA
FROM drawings
WHERE layerID = ?

And it all worked fine. So now I'm trying to do it in one step, so I try this:

这一切都很好。所以现在我试图一步到位,所以我试试这个:

SELECT DATA , layerID
FROM drawings
WHERE layerID = ANY (
  SELECT layerID
  FROM layers
  WHERE ownerID = ?
  AND collectionID = ?
)

But for some reason, it doesn't use the index, for the main query, SELECT DATA etc! So this one combined query takes much much longer to complete, versus the separate queries I was doing before. (By theway, the subquery, SELECT layerID etc still uses the index).

但由于某种原因,它不使用索引,主查询,SELECT DATA等!因此,这个组合查询需要花费更长的时间来完成,而不是之前我做过的单独查询。 (顺便说一句,子查询,SELECT layerID等仍然使用索引)。

I've determined if it's using a query or not by using the 'EXPLAIN' statement.

我已经通过使用'EXPLAIN'语句确定它是否正在使用查询。

I have individual indexes on the ownerID and collectionID columns in the layers table, and on the layerID column in the drawings table.

我在layers表中的ownerID和collectionID列以及图纸表中的layerID列上有各自的索引。

What am I doing wrong with my query?

我的查询错误是什么?

2 个解决方案

#1

Try a join. ANY ends up looking a lot like an unoptimizable UNION to the query optimizer.

尝试加入。 ANY最终看起来很像查询优化器的不可优化的UNION。

SELECT d.DATA, d.layerID  
FROM drawings AS d  
INNER JOIN layers AS l ON d.layerID = l.layerID  
WHERE l.ownerID = ? AND l.collectionID = ?

#2

I have never seen the ANY keyword before, but if you try

我以前从未见过任何关键字,但是如果你尝试的话

SELECT DATA , layerID
FROM drawings
WHERE layerID IN (
  SELECT layerID
  FROM layers
  WHERE ownerID = ?
  AND collectionID = ?
)

will that have the same problem? I believe it shouldn't. However, the INNER JOIN is probably a little bit better.

会有同样的问题吗?我相信不应该。但是,INNER JOIN可能会好一些。

#1