From: | Chris <dmagick(at)gmail(dot)com> |
---|---|
To: | ryan groth <postgres(at)cpusoftware(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Joins and full index scans...mysql vs postgres? |
Date: | 2006-02-22 23:58:39 |
Message-ID: | 43FCFAAF.1090404@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
ryan groth wrote:
> I am issing a query like this:
> SELECT *
> FROM users users
> LEFT JOIN phorum_users_base ON users.uid = phorum_users_base.user_id
> LEFT JOIN useraux ON useraux.uid = users.uid;
>
I'm not sure if postgres would rewrite your query to do the joins
properly, though I guess someone else might've already suggested this :)
I'm probably wrong but I read that as:
join users -> phorum_users_base (ON users.uid = phorum_users_base.user_id)
join phorum_users_base -> useraux (ON useraux.uid = users.uid) which
won't be indexable because u.uid doesn't exist in phorum_users_base.
Try
SELECT *
FROM users users
LEFT JOIN phorum_users_base ON users.uid = phorum_users_base.user_id
LEFT JOIN useraux ON useraux.uid = phorum_users_base.user_id
or
SELECT *
FROM users u, phorum_users_base pub, useraux ua WHERE u.uid =
pub.user_id AND au.uid = u.uid AND pub.user_id=au.uid;
--
Postgresql & php tutorials
http://www.designmagick.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Kings-Lynne | 2006-02-23 02:52:11 | Re: Joins and full index scans...mysql vs postgres? |
Previous Message | PFC | 2006-02-22 23:18:58 | Re: Joins and full index scans...mysql vs postgres? |