Hello Jason,

If "Select * from Table where a = :a and b = :b and (c = :c or 4=3)"

makes a speed difference over

"Select * from Table where a = :a and b = :b and (c = :c)",

aren't we setting ourselves up for a speed trap by sneakily taking
advantage of an IB6 weakness.

The moment a future version of IB6 gets some parsing smarts built in,
we'll have to carefully go thru all our SQL's, Procedures,Triggers,etc.

Thus:

1. Never Upgrade IB6 if your application gains a lot of speed
using the weakness.

2. Hope next IB6 release is a lot / lot smarter.

3. Somehow Localize all your SQL, Procedurer, Triggers somewhere
to quickly retest them for speed and change them, if required.

Rolling back denpendent procedures/triggers can be a joy.

Apart of listening to screaming clients, what else can we do ? :)

Best Regards
Hans

>Jason Wharton wrote:
>
>Hi Hans!

>>Is this caused by IB6 or IBO4 ?

>This is caused by IB, not IBO. The problem is that the IB optimizer >decides
>to use both indexes in case 1. Whenever I use selects on statements >like
>this, I try to tune my SQL to use one index for each table only. In >your example, I guess I would have tried either

>Select * from Table where a = :a and b = :b and (c = :c or 4=3)
>or Select * from Table where (a = :a or 3=2) and b = :b and c = :c

>depending on which index was the most selective (you want to use the >most selective index).

>Simple things like this can speed up the execution of selects a lot.