CODEWITHSUNDEEP
mysqloptimizationindexingsubquery
davr
davr

Reputation: 19147

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

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:

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).

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

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

What am I doing wrong with my query?

Upvotes: 0

Views: 1058

Answers (2)

jishi
jishi

Reputation: 24634

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.

Upvotes: 0

dkretz
dkretz

Reputation: 37655

Try a join. ANY ends up looking a lot like an unoptimizable UNION to 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 = ?

Upvotes: 5

Related Questions