On Tue, 27 Feb 2007 18:53:50 +0000 (UTC) Tuomo Valkonen <[EMAIL PROTECTED]>
wrote:
TV> On 2007-02-27, Sam Mason <[EMAIL PROTECTED]> wrote:
>> In more human terms I would have a table of files, the purpose of which
>> is to give you the fileid (sort of like the inode number), a table of
>> tags that define what tags are known, and a table containing the pairs
>> (tag,fileid) that define what files have what tags.
TV> Yeah, that's one quite simple layout, but how would you make the
TV> intersection of the search results for different tags then? A
TV> simple "same row" intersection doesn't work anymore. Does SQL
TV> have a more complicated intersection operation? And how efficient
TV> would it be? That's the bottleneck, I think. Plus there's no
TV> structure for quick access to a known file name, which would seem
SQL is pretty good about about searching, especially if you index your
columns well. Something like this, from the Oracle docs, should be
available in most good databases:
INTERSECT Example
The following statement combines the results with the INTERSECT
operator, which returns only those rows returned by both queries:
SELECT part
FROM orders_list1
INTERSECT
SELECT part
FROM orders_list2;
PART
----------
TAILPIPE
So for your case, it would be
SELECT fileid FROM tagset WHERE tag = 'A'
INTERSECT
SELECT fileid FROM tagset WHERE tag = 'B'
The known file name is just an extra column in the files table, and
searching for it is fast if the column is indexed.
Ted
