List:General Discussion« Previous MessageNext Message »
From:Robert DiFalco Date:May 24 2007 8:55pm
Subject:RE: Need confirmation: Subselects are broken with regards to index usage?
View as plain text  
Ok, so I guess it is more complicated than that.

This query which has 5M records that match its criteria returns
instantly:

 SELECT ELEMS.id
 FROM ELEMS 
 WHERE ((
 	ELEMS.nodeID IN (
 		SELECT link.childID 
 		FROM link 
 		JOIN path ON link.parentID=path.decendantId 
 		WHERE (path.ancestorId = 1)))) 
 LIMIT 0,100;

Now if I change the ancestorId criteria to a node group that does not
exist the query takes a very long time. Btw, it also looks like this is
being optimized into a less efficient EXISTS query. Anyway, the join
version doesn't have the same problem, it is fast if it is searching for
a conditions that has results or one that has none. Note that the JOIN
version requires a SELECT in case a node has multiple ancestors.

 SELECT DISTINCT ELEMS.id
 FROM ELEMS 
 JOIN link ON ELEMS.nodeID = link.childID
 JOIN path ON link.parentID=path.decendantId 
 WHERE (path.ancestorId = 1) 
 LIMIT 0,100;

Anyone have any ideas why this is the case? 



-----Original Message-----
From: Robert DiFalco [mailto:rdifalco@stripped] 
Sent: Thursday, May 24, 2007 1:11 PM
To: mysql@stripped
Subject: Need confirmation: Subselects are broken with regards to index
usage?

I think I'm discovering that sub-selects in MySQL are broken. Is that
true? It seems like you cannot have a sub-select without doing a table
scan -- even for a constant IN expression -- this because it gets
re-written as an EXISTS that executes for each row.
 
Is that true? Forcing an index doesn't even seem to help. 
 
R.

Thread
Need confirmation: Subselects are broken with regards to index usage?Robert DiFalco24 May
  • RE: Need confirmation: Subselects are broken with regards to index usage?Robert DiFalco24 May
    • Re: Need confirmation: Subselects are broken with regards to indexusage?Baron Schwartz24 May
      • RE: Need confirmation: Subselects are broken with regards to index usage?Robert DiFalco26 May