> 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
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/
