From: Peter Brawley
Date: February 11 2008 10:18am
Subject: Re: Inefficient query processing?
List-Archive: http://lists.mysql.com/mysql/211291
Message-Id: <47B0210E.8080502@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Yves

 >it will rather find messages that have no tag with a keylist
 >which does not include the currently logged in user's UserId
 >or one of this user's additional keys, which are again stored
 >in a keylist.

Unclear.

PB

Yves Goergen wrote:
> On 11.02.2008 00:29 CE(S)T, Peter Brawley wrote:
>> message (messageID)
>> keylist (keylistID)
>> tag ( tagID, readaccesskeylist references keylist(keylistID) )
>> message_revision_tag ( ???, messageID references message(messageID), 
>> tagID references tag(tagID))
>
> Another table:
> message_revision(MessageId references message, RevisionNumber)
>
> Correction:
> message_revision_tag(MessageId, RevisionNumber, TagId)
>   (MessageId, RevisionNumber) references message_revision
>
>> (i) Finding messages which have a deny-access tag looks like a simple 
>> join:
>>
>> SELECT DISTINCT messageID
>> FROM message_revision_tag AS mrt
>> JOIN tag AS t ON mrt.tagID=t.tagID
>> WHERE t.readaccesskeylist IS NOT NULL;
>>
>> (ii) We get the messages not in the above result with a simple 
>> exclusion join:
>>
>> SELECT messageID
>> FROM message m
>> LEFT JOIN (
>>   SELECT DISTINCT messageID
>>   FROM message_revision_tag AS mrt
>>   JOIN tag AS t ON mrt.tagID=t.tagID
>>   WHERE t.readaccesskeylist IS NOT NULL
>> ) AS banned USING (messageID)
>> WHERE banned.messageID IS NULL,
>>
>> Or did I miss something?
>
> I'm afraid I cannot integrate this in my large query. It looks too 
> simple and I don't know where to put its parts. Maybe I'll really have 
> to show the full schema and the complete query...
>
> It's not only that my entire query will find messages that have no tag 
> with a ReadAccessKeylist assigned; it will rather find messages that 
> have no tag with a keylist which does not include the currently logged 
> in user's UserId or one of this user's additional keys, which are 
> again stored in a keylist.
>