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