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:[EMAIL PROTECTED]
Sent: Thursday, May 24, 2007 1:11 PM
To: mysql@lists.mysql.com
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
