List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:May 24 2007 9:34pm
Subject:Re: Need confirmation: Subselects are broken with regards to index
usage?
View as plain text  
Hi Robert,

The way non-correlated subqueries are sometimes "optimized" into correlated ones 
and then executed for each row in the outer table is a well-known MySQL 
deficiency, yes.  I would not really look for it to be fixed soon, though it's 
been in progress for a while.  The version in which it gets fixed is still 
likely a long way from GA.  But maybe a MySQL employee can give better info on that.

In the meantime, we are all forced to find alternative ways to write such 
queries :-)

Baron

Robert DiFalco wrote:
> 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