At 03:14 PM 11/22/2004, Salim Naufal wrote:

>SELECT K1.PKEY
>FROM KEYWORDS K1
> JOIN KEYWORDS K2 ON (K1.PKEY = K2.PKEY)
> JOIN KEYWORDS K3 ON (K1.PKEY = K3.PKEY)
>WHERE (K1.KWORD = 'first keyword')
> AND (K2.KWORD = 'second keyword')
> AND (K3.KWORD = 'third keyword')
>
>For this, I have created three indexes:
> Index1: KEYWORDS(KWORD)
> Index2: KEYWORDS(PKEY)
> Index3: KEYWORDS(KWORD, PKEY) <- UNIQUE

You probably don't need index1 - certainly won't in Firebird 2
where selectivity is maintained by key segment. But there, I'd
probably drop index3 since the problems with non-selective indexes
are largely fixed.

>Given the large number of rows in the KEYWORDS table, how should the
>keywords be ordered?
>1) set the 'first keyword' that results in the smallest result set?
>2) set the 'third keyword' that results in the smallest result set?

In theory, the optimizer will get that right. In fact, it probably
will. You should be able to express your query in the way that makes
sense to you without worrying about the effect on the optimizer.

>Second Question:
>I have created a secondary table that contains the keyword words and number
>of occurrences. Ultimately such a table can be populated with a simple
>query:
>
>INSERT INTO KEYWORDS_STATS
>SELECT COUNT(*), KWORD
>FROM KEYWORDS

Errr.... in the message, you've left off the group by that you
have in your running query, right?

Regards,


Ann