Re: Joins on many-to-many relations.

At 11:39 AM 3/14/07, Wiebe Cazemier wrote:
>Consider this scenario of three (simplified) tables:
>
>people
>- id
>- name
>
>accounts
>- id
>- owner_id REFERENCES people
>
>account_co_owners
>- co_owner_id REFERENCES people
>- account_id REFERENCES accounts
>
>I need a query that allows the user to search for accounts by giving names of
>either co-owners or owners. Currently, the query responsible is this:
>
>SELECT DISTINCT ON (account.id) account.*
>FROM accounts AS account
>INNER JOIN people AS owner
> ON owner.id = account.owner_id
> OR owner.id IN (SELECT co_owner_id
> FROM account_co_owners
> WHERE account_id = account.id
> AND co_owner_id = owner.id)
>WHERE owner.name LIKE '%user supplied search string%';
>
>But this query is too slow for my taste.

A performance question should always include the output of EXPLAIN ANALYZE.

I think the problem is database design. If you added a boolean column into
accounts table which would indicate owner/co-owner; then all data from
account_co_owner could be merged into accounts and the query would be much
simpler to code.

I don't expect this code to be any quicker; but I think it more clearly
identifies the problem with your design:

SELECT accounts.* from accounts
inner join
( SELECT account.* FROM
( select id,owner_id from accounts
union
select account_id,co_owner_id from account_co_owners
) as account
INNER JOIN
( SELECT id FROM people WHERE name LIKE '%user%' ) AS owner
on account.owner_id = owner.id
) as acct on acct.id=accounts.id;