Re: slow view

On 2006/10/11 Stuart wrote:
> [..]

Apologies for following up my own post, but after
struggling with that query for over a day, I figured
out the answer within thirty minutes of posting.
(Sigh)

My slow query was:
> SELECT p.id AS pid, a.id AS aid, sub.bid AS bid
> FROM p
> JOIN a ON a.p=p.id
> LEFT JOIN (
> SELECT a.id AS aid, b.id as bid
> FROM a
> LEFT JOIN b ON b.p=a.p
> LEFT JOIN x ON x.a=a.id AND x.b=b.id
> WHERE x.a IS NULL
> ) AS sub ON sub.aid=a.id
> WHERE p.id=1;

Including the parent table p in the inner select was
all it took.

SELECT p.id AS pid, a.id AS aid, sub.bid AS bid
FROM p
JOIN a ON a.p=p.id
LEFT JOIN (
SELECT p.id AS pid, a.id AS aid, b.id as bid
FROM p
JOIN a ON a.p=p.id
LEFT JOIN b ON b.p=a.p
LEFT JOIN x ON x.a=a.id AND x.b=b.id
WHERE x.a IS NULL
) AS sub ON sub.aid=a.id AND sub.pid=p.id
WHERE p.id=1;