Subject Re: about nocase collation
Author Adam
--- In, "donoteatcarrot"
<donoteatcarrot@y...> wrote:
> Hello, Ivan.
> Thank you very much for your reply.
> What dose "Unless you need to use index" means ?

If a field f is indexed, and you run a query that is equivalent to
starting with, then that index can be used to quickly determine which
records meet that criteria. On a large table, it can mean the
difference between 30ms and 5 minutes or longer. For a small table,
the difference is not significant.

if you had a query like

select id
from customer
where name like 'A%'

(which is equivalent to a starting with condition)

then if an index was available on the name field, and that index had a
good selectivity, then a table scan is avoided and the index is used
to narrow down to the records that match that criteria. All your
transaction has to do now is identify which of the records it can see
and return them.

But if you changed the query to

select id
from customer
where Upper(name) like 'A%'

Then (in FB1.5 at least), the index can not be used, even if it is
available. FB 2 allows you to create an index on an expression such as

What Ivan was describing is possibly a work around to your problem
with collation. If your table is relatively small, and absolute
performance is not necessary, then you can "get away with" what he has