Hi

Thanks Adam. Yes the IN clause is the bottleneck. I have just solved ?
the issue by creating a stored procedure that handles the IN part so my
query now looks like...


SELECT bk.ISBN13, bk.FTI
FROM Book bk
JOIN Pr_BookWordAndSearch('WET', 'WILD', NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL) p ON p.ISBN13 = bk.ISBN13


This produces records in .1 < ms and should work great.

The only downside (apart from shear ugliness) is that it limits our code
to up to 10 search words.

p.s. your solution query didn't improve the situation.


Rob Martin
Software Engineer

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

Wild Software Ltd



Adam wrote:

> <snip>
>
>
>> 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)
>>
>
> <snip>
>
>
>> PLAN SORT (JOIN (KW INDEX (KEYWORD_WORD,KEYWORD_WORD),BKW INDEX
>> (BOOKKEYWORD_PK)))
>> PLAN (BK NATURAL)
>>
>
> Hi Rob,
>
> The problem here is that the optimiser is doing a natural walk through
> the book table, and from each record doing an exists check. As the
> number of book records increase, this will get linearly slower.
>
> The only thing (in pure SQL anyway) that I can think of that might
> give the optimiser the appropriate hint is:
>
> 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)
>
> (assuming ISBN is a string, use +0 if a numeric or time based type)
>
> Obviously not tested. It just prevents the BOOKKEYWORD_PK index which
> should make the better plan more attractive if the optimiser is clever
> enough.
>
> Adam
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>