On 11/23/2011 01:33 PM, Mark Rotteveel wrote:

> 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 and Thomas,

Thanks for your pointers. Tested it with IBExpert, now I just have to
see which is the best/easiest approach to use with Python/SQLAlchemy

Werner