On Wed, 23 Nov 2011 12:11:52 +0100, "Werner F. Bruhin"
<werner.bruhin@...> wrote:

> I do a query like this:
>
> SELECT subregion.NAME
> FROM subregion
> WHERE subregion.name collate UNICODE_CI_AI starting with 'abona'
>
> And I get a plan:
> PLAN (SUBREGION NATURAL)
>
> I tried with these indexes, obviously would like to end up with just one

> of them):
>
> CREATE INDEX SUBREGION_IDX1 ON SUBREGION (NAME);
> CREATE INDEX SUBREGION_IDX2 ON SUBREGION COMPUTED BY (lower(NAME));
> CREATE DESCENDING INDEX SUBREGION_IDX3 ON SUBREGION (NAME);
>
> 'subregion' is a table ultimately I will have a view for subregion which

> brings in another table, but still like to search by name and preferably

> with the use of an index.
>
> This is with FB 2.5.
>
> Looking for tips and/or documentation links to read for better
> understanding on when indexes are used or what to do to get them used.

It cannot use the normal index because (most likely) the collation of the
column definition is different from this collation. As such the index is
not usable for the query.

There are two options:
1) Specify the collation in the column definition (then there is no need
to specify it in the query)
2) Create an expression index which includes the collation and use the
same expression in your query

eg
CREATE INDEX SUBREGION_IDX2 ON SUBREGION COMPUTED BY (lower(NAME collate
UNICODE_CI_AI))
and
WHERE (lower(NAME collate UNICODE_CI_AI)) starting with 'abona'

It should also be possible to specify an expression index for only the
collation:
CREATE INDEX SUBREGION_IDX2 ON SUBREGION COMPUTED BY (NAME collate
UNICODE_CI_AI)
(and now you should be able to use the original query)

See:
http://firebird.1100200.n4.nabble.com/FB2-expression-indexes-and-collations-td1110287.html

Mark