hi- i have two tables for a web index:

keywords:
-keywordid(int)
-keyword(text)

and

keywordurls
- keywordurlid
- keywordid
- urlid

I want to perform a simple query, where I find all the urls containing
a keyword:

select urlid from keywordurls where keywordid in
(select keywordid from keywords where keyword like '%searchterm%')

The problem is, I cannot create an index on keywordurls that makes the
search be the cardinality of the subquery results, it's always the
cardinality of the keywordurls table.

I've tried an index on keywordid, I've tried an index on urlid, and
two joint indexes - keywordid-urlid and urlid-keywordid

So, to me, it would seem that the subquery would execute first,
returning, say, X values, and which are keywordid's, and then if I
have a keywordid index, it would only have to find X values. But,
like I said, the explain command always says the search will be order
of the cardinality of the entire table!

Thanks for any help,
Dino