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.idAS pid, a.id AS aid, b.id as bid               FROM p                  JOIN a ON a.p=p.id                  LEFT
JOINb 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.idAND sub.pid=p.id   WHERE p.id=1;