On 11/23/2011 01:33 PM, Mark Rotteveel wrote: > On Wed, 23 Nov 2011 12:11:52 +0100, "Werner F. Bruhin" > <[email protected]> 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
