Hi

We have the following structure...

Book (ISBN13 PK, ....) 50000 recs

BookKeyWord (ISBN13, WordRef (CPK)) -> We have also created an index on
WordRef as this improve performance on some queries. 790000 recs

KeyWord (WordRef PK, Word) -> Word is indexed. 81000 recs


We are trying to write a query that will return a list of books where
the book contains all of a list of words. We have the following attemp


SELECT bk.ISBN13, bk.FTI
FROM Book bk
WHERE bk.ISBN13 IN (

SELECT bkw.ISBN13
FROM KeyWord kw
JOIN BookKeyWord bkw ON bkw.WordRef = kw.WordRef
WHERE kw.Word = 'WET'
OR kw.Word = 'WILD'
GROUP BY bkw.ISBN13
HAVING COUNT(bkw.ISBN13) = 2)


Where this SQL will be generated in code. It takes 4 secs with the
following plan

PLAN SORT (JOIN (KW INDEX (KEYWORD_WORD,KEYWORD_WORD),BKW INDEX
(BOOKKEYWORD_PK)))
PLAN (BK NATURAL)

The problem appears to be the index on ISBN13 on Book isn't being used.
If I create a view based on the select used in the 'IN' part of the
above and then join this view to book the performance is ms and all
indexes are used.

I have considered using UNION but can't find Union clause that only
returns the intersection of two (or more) SQLs.

I would greatly appreciate any suggestions on how to improve this.
Ironically it is currently faster to create a temporary view, join of
this, display results and then delete the view !

We are expecting the data to be significantly larger in the future.


--


Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd