Re: Help with composite indices

Ian Goddard <igoddard@netcomuk.co.uk> wrote: >X-Informix-List-Id: <news.35075> > >The ordering of columns in the index is critical. > >Assume you have an index on columns A, B and C - listed in that order in >the CREATE INDEX statement. > >As I understand it you can, in either, SELECT or ORDER BY, usefully >refer to A alone, A and B or A, B and C. You can't usefully refer to B >or C alone or B and C. If you refer to A and C only, then the index >will only be used for A. In other words, if there's a gap in your >references to the columns then the columns after the gap won't get used. > >That right, johnl? You called? Basically, yes, that's right. There are some additional restrictions, such as that if your criterion on A is 'A != "some-value"', then the optimiser still can't (or won't) use the index, even if you also mention B and C. Similarly with leading wild cards in MATCHES or LIKE operators, and probably a few other cases too. For example, GLS/NLS searches (ie locale-specific, rather than ASCII order) may not be able to use the index fully (as an accented characters may be completely out of ASCII sequence but still within the search range). >Bradford Young wrote: >> I've read how to create a composite index, but I'm unsure about >> how to use one to order output or join tables. In my db, I have >> tables with company id's and dates, with the combination of >> id and date being unique. So I can create an index on (id, date). >> Can I use that index for joining tables? >> Can I use that index when I come to the "order by" part of a >> select statement? You can't control whether the database engine uses the index or not. You provide the index in the hope that it will, but you can't force it to use any particular index. Given an index on (id, date), if your join condition specifies both id and date (which it presumably would), then the index will probably be used in joining tables. All other things being equal, if the ORDER BY clause cites the id and date (in that order, with no prior sort columns), then maybe the engine will be able to use the index to save itself a separate sort phase (but maybe it won't be able to use the index). It'll depend on what else is being processed with whichever table it is that your ordering on. Yours with lots of prevarications, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>