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
fromaccount_co_owners    ) as account    INNER JOIN    ( SELECT id FROM people WHERE name LIKE '%user%' ) AS owner
onaccount.owner_id = owner.id  ) as acct on acct.id=accounts.id;