OK, so I suppose the main thing here is that using LIKE doesn't prevent an
index being used.

Maybe I didn't explain clear enough.

Given the situation that in field1 all records starting with abcd are abcde
is there then any advantage to do

field1 = 'abcde' compared to doing field1 starting with 'abcd' ?



RBS



_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: 24 September 2009 01:07
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Speed difference between = and LIKE





At 08:59 AM 24/09/2009, you wrote:

>In Firebird 1.5 is there any difference in speed between these 3 queries if
>there in an index on field1, which is a fixed size (5) text field and if

the

>produced number of records will be the same:
>
>select field1 from table1 where field1 = 'abcde'
>
>select field1 from table1 where field1 like 'abcd%'
>
>select field1 from table1 where field1 starting with 'abcd'
>
>It looks not, but just want to make sure.

Because the engine transforms a LIKE predicate with a trailing wild card
into a STARTING WITH predicate, these two are equivalent and can use the
index if the optimizer considers it would help:

select field1 from table1 where field1 like 'abcd%'

select field1 from table1 where field1 starting with 'abcd'

This one is not equivalent to the others since, unlike them, it will match
only one value of field1:

select field1 from table1 where field1 = 'abcde'

./heLen





[Non-text portions of this message have been removed]