Re: SQL-based search engine

From: Heinz Huber <Heinz.Huber_at_elbanet.co.at>
Date: 2000/02/25
Message-ID: <38B699C5.373D24BD_at_elbanet.co.at>#1/1

Tore Aursand wrote:
>
> In article <38B63766.10665B8F_at_elbanet.co.at>,
> Heinz.Huber_at_elbanet.co.at says...
> > SELECT a.ArticleID, a.Date, a.Header, COUNT(*) as Relevance
> > FROM tblArticle a, tblArticlKeyword ak, tblKeyword k
> > WHERE a.ArticleID = ak.ArticleID AND ak.KeywordID = k.KeywordID AND
> > Keyword IN ("Key1", "Key2", "Key3")
> > GROUP BY a.ArticleID, a.Date, a.Header
> > ORDER BY Relevance DESC;
>
> Seems like a good suggestion, but I haven't been able to make
> it work yet. Searching for only *one* keyword works find, and
> it's extremely fast (of course).
>
> However, adding only one more keyword - ie. two alltogether -
> makes MySQL hang. Does this sound like a bug, or does adding
> another keyword make the query much more complex for the SQL
> engine?

AFAIK, the IN should be translated to ORs by the engine. If you have troubles using IN, you could consider using the OR yourself (don't forget parentheses though). If that still poses a problem, try the query below.

I've also come up with an alternative query which uses an index on tblKeyword.Keyword alone (this way more of the keyword fits into the index):

SELECT a.ArticleID, a.Date, a.Header, COUNT(*) as Relevance FROM tblArticle a, tblArticlKeyword ak
WHERE a.ArticleID = ak.ArticleID AND ak.KeywordID IN

    (SELECT KeywordID

     FROM tblKeyword
     WHERE Keyword IN ("Key1", "Key2", "Key3"))

GROUP BY a.ArticleID, a.Date, a.Header
ORDER BY Relevance DESC;

Since the subselect should return only a few rows, performance might improve.

Glad to help
Heinz Received on Fri Feb 25 2000 - 00:00:00 CET