Dear Group,

I've heard that using the IN (subquery) clause is not very efficient, since it doesn't make use of 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;

TABLE Records have
RecordID INTEGER
RecordNAME VARCHAR(200)
OwnerID INTEGER
ReadRights INTEGER
RecordStatus INTEGER

TABLE RecordReadRights have
RecordID INTEGER
UserID INTEGER

Here is the explanation:
If ReadRights
= 0 - Everyone can read the record
= 1 - Only the owner can read the record
= 2 - Only given users (from RecordReadRights table) can read the record, this is why IN is used.

Any ideas are welcome!

Thank you!


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