From: ProgHome <proghome@silesky.com>
To: 'Franco Bruno Borghesi' <franco@akyasociados.com.ar>
To: pgsql-sql@postgresql.org
Subject: Re: How to optimize this query ?
Date: Fri, 15 Aug 2003 16:24:24 -0400
Message-ID: <005801c3636b$37ef0e50$0700a8c0@Office3> (raw)
In-Reply-To: <1060791455.1082.1.camel@taz.oficina>
As I am using mysql 4.0 right now (we've got a stupid problem with the
4.1 with the authentification protocol we can't figure out) and the last
subquery (the one in the last LEFT JOIN) MUST be removed .
So I tried the following query:
SELECT
L.*
FROM lead L
LEFT JOIN purchase P1 ON ( L.id = P1.lead_id )
LEFT JOIN affiliate_lockout A ON ( L.affiliate_id =
A.affiliate_locked_id )
LEFT JOIN (
purchase P2
INNER JOIN member_exclusion M ON ( P2.member_id =
M.member_id_to_exclude)
) ON ( L.id = P2.lead_id )
WHERE UNIX_TIMESTAMP( now( ) ) - UNIX_TIMESTAMP( date_creation ) <= ( 6
* 24 * 3600 ) AND (
exclusive IS NULL OR (
exclusive = 0 AND nb_purchases < 3
)
) AND (
A.member_id <> 21101 OR A.member_id IS NULL )
AND ( P1.member_id <> 21101 OR P1.member_id IS NULL )
But it seems that the LEFT JOIN doesn't work anymore and are replaced by
OUTER JOIN because the result of the query is (number of rows in Lead *
number of rows in PURCHASE * number of rows in .)
And it seems that the condition L.id = P2.lead_id doesn't work either .
Could you tell me what the problem is ?
Thanks
-----Original Message-----
From: Franco Bruno Borghesi [mailto:franco@akyasociados.com.ar]
Sent: Wednesday, August 13, 2003 12:18 PM
To: proghome@silesky.com; pgsql-sql@postgresql.org
Subject: Re: [SQL] How to optimize this query ?
Maybe its better now. Anyway, what I think is that joining will perform
better than using IN. Am I wrong?
SELECT
L.*
FROM
lead L
LEFT JOIN purchase P ON (L.id=P.lead_id)
LEFT JOIN member_exclusion M ON
(P.member_id=M.member_id_to_exclude)
LEFT JOIN (
SELECT DISTINCT affiliate_locked_id FROM affiliate_lockout WHERE
member_id=21101
) A ON (L.affiliate_id=A.affiliated_locled_id)
WHERE
L.exclusive IS NULL OR
(
L.exclusive=0 AND
L.nb_purchases<3
) AND
(P.lead_id IS NULL OR P.lead_id<>21101) AND
(M.member_id IS NULL) AND
(A.member_id IS NULL)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: proghome@silesky.com, franco@akyasociados.com.ar
Subject: Re: How to optimize this query ?
In-Reply-To: <005801c3636b$37ef0e50$0700a8c0@Office3>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox