Clever Geek Handbook

Why isn't MySQL using an index for this subquery?

I used this:

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

Which would give me an array of layerIDs, and then I would loop and do this for each one:

SELECT DATA
FROM drawings
WHERE layerID = ?

And everything worked out great. So now I am trying to do it in one step, so I am trying 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

! Thus, this merged request takes much longer than completion compared to the separate requests I have done before. (By the way, the subquery SELECT layerID etc

still uses the index).

I have determined whether it is using a query or not using the EXPLAIN statement.

I have separate indexes on columns ownerID

and collectionID

table layers

and column layerID

in the table drawings

.

What am I doing wrong with my request?

0


source to share


2 answers


Try to join. EACH end looks a lot like an unoptimizable UNION on the query optimizer.



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 = ?
+5


source


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 it be the same problem? I believe that this should not be the case. However, INNER JOIN is probably slightly better.

0


source






More articles:


All Articles