That's a little surprise, I know the index is essential for kw1, but
thought it would slow down if used in the subselects - the thought being
that an ISBN13 value has few keywords (for bkw1, the ISBN13 index is
useless, whereas it for bkw2 could be brilliant), whereas the words
themselves could be a keyword in more books. Though the English language
contains a lot of words, so I guess I shouldn't be all too surprised.

Anyway, nice to hear that it has acceptable performance,
Set

Robert martin wrote:

> Your original solution is good. Performance isn't too bad on 3m
> records. Turning off the index below kills the performance however.
>
> Thanks for your help :)
>
> Rob Martin
> Software Engineer
>
> phone +64 03 377 0495
> fax +64 03 377 0496
> web www.chreos.com
>
> Wild Software Ltd
>
>
>
> Svein Erling Tysvaer wrote:
>> Hmm, assuming that the keyword exists in more books than each book has
>> keywords, you may try optimizing a bit further:
>>
>> SELECT bk.ISBN13, bk.FTI
>> FROM Book bk
>> JOIN BookKeyWord bkw1
>> ON bk.ISBN13 = bkw1.ISBN13
>> JOIN KeyWord kw1
>> ON bkw1.WordRef = kw1.WordRef
>> WHERE kw1.Word = 'WET'
>> AND EXISTS(
>> SELECT *
>> FROM BookKeyWord bkw2
>> JOIN KeyWord kw2
>> ON bkw2.WordRef = kw2.WordRef
>> WHERE bk.ISBN13 = bkw2.ISBN13
>> AND kw2.Word||'' = 'WILD')
>>
>> Though this is just a guess, I haven't seen your plan and don't know
>> about the selectivity of your indexes.
>>
>> Set