Zd wrote:

> 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!

Hi,
you can try

SELECT R.RecordID, R.RecordName FROM Records R
WHERE (R.OwnerID = :uid
OR R.ReadRights = 0
OR (R.ReadRights = 2
AND EXISTS(SELECT * FROM RecordReadRights RRR
WHERE R.RecordID = RRR.RecordID
AND RRR.UserID = :uid)))
AND R.RecordStatus >= 0;

or, alternatively (if RecordReadRights doesn't contain duplicates),

SELECT R.RecordID, R.RecordName
FROM Records R
LEFT JOIN RecordReadRights RRR ON R.RecordID = RRR.RecordID
AND R.OwnerID = RRR.UserID
WHERE (R.OwnerID = :uid
OR R.ReadRights = 0
OR (R.ReadRights = 2
AND RRR.UserID IS NOT NULL))
AND R.RecordStatus >= 0;


I don't know whether any of these are quicker than your original SQL or
not, at some point Firebird started to translate IN into EXISTS whenever
possible. If the combination (RecordID, UserID) is the primary key or an
index in RecordReadRights, then this shouldn't slow down the query
execution too much, but you do have two OR's when selecting from your
Records table and I assume that OwnerID and ReadRights aren't all too
selective. So if this table contains lots of records (several millions)
and it is common for RecordStatus to be greater than zero, then this may
slow down the query a bit (since I assume that Records will be the first
query in the plan, it shouldn't be all too slow unless you have a lot of
concurrent users and demand instant results).

HTH,
Set