Re: SQL-based search engine

Hi!

Tore Aursand wrote:
>
> Hi!
>
> I've got a problem with a search engine I'm creating. I'm
> using MySQL, and have got three tables which looks roughly like
> this (you should have fixed font size to read this);
>
> tblArticle tblArticlKeyword tblKeyword
> ---------- ---------------- ----------
> ArticleID <--> ArticleID +-> KeywordID
> Date KeywordID <-------+ Keyword
> Header
>
> Now, I want to search for keywords in all the articles, but I'm
> not sure of the fastest way to do that.
>
> Preferably, I would like my search engine to accept multiple
> words, and thereafter sort the result on relevance (ie. the
> articles with most of the keywords in it will be listed first).
>
[snip]

First, create unique indexes on the Keys (for tblArticlKeyword, all columns!). For considerations which kind of index you should use, I'll have to leave this to others. You might also want to include the column Keyword for tblKeyword.

Then do a query like:

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;

I'm not totally sure about the ORDER BY. You might have to use "4" instead of "Relevance".

Heinz Received on Fri Feb 25 2000 - 00:00:00 CET