> 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

As this is a pure Firebird database issue in respect to collations and
indexes, I guess this is independent of the access technology you use?
Or does Python/SQLAlchemy generates your mentioned SQL statement behind
the scene?


--
With regards,
Thomas Steinmaurer

* Upscene Productions - Database Tools for Developers
http://www.upscene.com/

* My Blog
http://blog.upscene.com/thomas/index.php

* Firebird Foundation Committee Member
http://www.firebirdsql.org/en/firebird-foundation/