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