--- In firebird-support@yahoogroups.com, Bart Smissaert wrote:

> Have seen a situation now where there is big performance difference
> between like 'Z%' and starting with 'Z' due to it picking a very
> different query plan.
> This is with a normal, direct SQL (done in Database Workbench and
> also via ADO and ODBC), so not with any parameters.

Sure, the optimizer should be the same regardless of whether you use DBW, ADO or ODBC.

> The queries that give the different query plans here are:
>
> SELECT
> K.TERM_KEY,
> R.READ_CODE,
> R.TERM30,
> R.USAGE_COUNT
> FROM
> KEYWORD K
> INNER JOIN READCODE R ON
> (K.TERM_ID = R.TERM_ID)
> WHERE
> K.TERM_KEY STARTING WITH 'Z'
>
> query plan:
> JOIN (R NATURAL,K INDEX (KEYWORD_KEY,KEYWORD_KEY2))
>
> and
>
> SELECT
> K.TERM_KEY,
> R.READ_CODE,
> R.TERM30,
> R.USAGE_COUNT
> FROM
> KEYWORD K
> INNER JOIN READCODE R ON
> (K.TERM_ID = R.TERM_ID)
> WHERE
> K.TERM_KEY LIKE WITH 'Z%'
>
> query plan:
> JOIN (K INDEX (KEYWORD_KEY),R INDEX (READCODE_KEY))
>
> The second query plan is the right one and run much faster than the
> first. This is all on Firebird 1.5 and I expect that Firebird 2
> handles this better, but we can't change Firebird.
>
> Now, my questions are:
> Is there any way we can deduct when to do like or starting with in
> this kind of query.

No, not with any reasonable effort. I thought they would be identical, but, unlike Helen, I didn't know that change came about in 1.5.6 (I thought it was 1.5.0 or something). Now, I am wondering whether LIKE always chooses the same index as you report or if it was coincidental that it is better than STARTING WITH.

> Is there a simple way to force the right query plan?

Well, almost. If you add ||'' it will prevent the KEYWORD_KEY2 index from being used and be force the optimizer to choose differently (although, theoretically, it might come up with another bad plan):

FROM KEYWORD K
INNER JOIN READCODE R ON K.TERM_ID||'' = R.TERM_ID
WHERE K.TERM_KEY STARTING WITH 'Z'

> Is this indeed dealt with better in Firebird 2?

I cannot tell for this particular query, but the need for hacks like +0 or ||'' has gradually been reduced and the optimizer is generally better now than it was with Firebird 1.5.

HTH,
Set