Thanks for the quick reply Adam

What about

where (CODE between 'AAAA' and 'BBBB')

and

where (CODE >= 'AAAA' and CODE <= 'BBBB')


Hint: All those conditions I use are on varchar fields and non of them on
numerical fields.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]On Behalf Of Adam
Sent: Wednesday, December 28, 2005 9:22 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: usage of = and between in queries


--- In firebird-support@yahoogroups.com, Vahan Yoghoudjian
<vahan@p...> wrote:

>
> Hello all
>
> First of all I want to wish you all the best for the new year....
>
> Is there any differences in the usage of indexes if in the query

if I

> say
>
> Method1: where (CODE = 'ABCD')
> Method2: where (CODE between 'ABCD' and 'ABCD')
>
> The main reason for this is that I have functions that write

conditions

> of some queries at run time according to the selected values from

two Lookup

> combos by the user (FROM and TO). So far in these functions I am not
> checking if those two values are equal, whatever the values are (of

course

> only if TO >= FROM) the second method is used to write the condition...
> If in thise case the first method has better performance I will

adjust

> the functions in order to generate this method if both values are equal
>

That will be fine. Even if FB doesn't internally optimise it, it is an
additional hit to a cached index so it wont be any slower than what
you are doing at the client side. Between is a lot better than IN
(where possible)

eg

where somefield between 1 and 5

is a lot more efficient than

where somefield in (1,2,3,4,5)

Adam







++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net 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