List:General Discussion« Previous MessageNext Message »
From:Yves Goergen Date:February 11 2008 9:31am
Subject:Re: Inefficient query processing?
View as plain text  
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.

-- 
Yves Goergen "LonelyPixel" <nospam.list@stripped>
Visit my web laboratory at http://beta.unclassified.de
Thread
Inefficient query processing?Yves Goergen10 Feb
  • Re: Inefficient query processing?Peter Brawley11 Feb
    • Re: Inefficient query processing?Yves Goergen11 Feb
      • Re: Inefficient query processing?Peter Brawley11 Feb
        • Re: Inefficient query processing?Yves Goergen11 Feb
          • Re: Inefficient query processing?Peter Brawley11 Feb
            • Re: Inefficient query processing?Yves Goergen11 Feb
              • Re: Inefficient query processing?Peter Brawley11 Feb
                • Re: Inefficient query processing?Yves Goergen11 Feb
  • Re: Inefficient query processing?Perrin Harkins11 Feb
    • Re: Inefficient query processing?Yves Goergen11 Feb
      • Re: Inefficient query processing?Perrin Harkins11 Feb
      • Re: Inefficient query processing?Peter Brawley11 Feb