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
