Re: Joins and full index scans...mysql vs postgres?

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/