Thank you for your reply Helen.
Putting RecordStatus first is a good idea, I'll use it.

Is there a way to make this query using JOIN? Would JOIN be faster than EXISTS?

-Zd

----- Original Message -----
From: Helen Borrie
To: firebird-support@yahoogroups.com
Sent: Wednesday, August 06, 2008 12:21 PM
Subject: Re: [firebird-support] Please help me with this query


At 06:14 PM 6/08/2008, you wrote:
>Dear Group,
>
>I've heard that using the IN (subquery) clause is not very efficient, since it doesn't make use of indices.

Not true. NOT IN () doesn't use indices.

>Could you please help me optimize the following query? Can this be done with JOIN?
>
>SELECT RecordID, RecordName FROM Records WHERE (OwnerID = :uid OR ReadRights = 0 OR
>(ReadRights = 2 AND RecordID IN (SELECT RecordID FROM RecordReadRights WHERE UserID = :uid))) AND RecordStatus >= 0;

SELECT
rec.RecordID,
rec.RecordName FROM Records rec
WHERE
rec.RecordStatus >= 0
AND (
rec.OwnerID = :uid
OR rec.ReadRights = 0
OR (
rec.ReadRights = 2
AND EXISTS (
SELECT 1 FROM RecordReadRights rrr
WHERE rrr.RecordID = rec.RecordID
and rrr.UserID = :uid
)
)
);

This really doesn't change anything or make it "more optimised", except that putting the RecordStatus test first might eliminate wasted executions of the costly correlated subquery. The recoding above corrects your syntax for a multiple-table query.

The Firebird engine will resolve your IN (SELECT..) expression to the above EXISTS(..) expression anyway, but you might save a few CPU cycles by presenting it "ready to roll".

./heLen




[Non-text portions of this message have been removed]