> 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);
This index should be enough when using STARTING WITH, although I guess the guilty part in your statement is the COLLATE clause, which prevents the optimizer to use the index. Just a wild guess. -- 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/ > 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. > > Werner > > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > > Also search the knowledgebases at http://www.ibphoenix.com > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Yahoo! Groups Links > > >
