Dear Set,

Thank you for your reply.

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;

Do you think exists is faster than in?

To answer some of your questions:
In RecordReadRights, RecordID and UserID are primary keys, there are no other fields in the table.
In the Records table, RecordID is primary key, and there are separate indices on ReadRights and RecordStatus.

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 understand your second query, sorry. Why is the "AND R.OwnerID = RRR.UserID" line needed and why is there the NOT NULL part?

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.

What do you mean by OwnerID and ReadRights not being too selective?

The Records table isn't likely to have more than 100.000 records in the near future. At maximum 30 people will be accessing the database, and no more than 5 of them will use the query simultaneously.

Thanks:
Zd

----- Original Message -----
From: Svein Erling Tysvaer
To: firebird-support@yahoogroups.com
Sent: Wednesday, August 06, 2008 11:30 AM
Subject: Re: [firebird-support] Please help me with this query


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




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