Re: Why composite indexes ?

In article <01bd687f$428e4fc0$190210ac@vodachois2040.vodac.co.za>, Spock <dirkm@vodac.co.za> writes >Hi, > >I did a check last night to compare 2 selects using > >a) an index on one column only, ie. not a composite index (I updated stats >HIGH on this column) > >and >b) a secondary column in a composite index (also updating stats HIGH on >this column). I used the same column for both tests. > >The first select was immediate, but the second select, even though I was >using an index, took long before it returned a result. > >My question is, why do I use composite indexes if it is faster to select on >indexes created on one column only ? Because what happens when you are selecting on >1 column? The composite indexes are used to speed up searches on the initial columns of the index e.g. create index djw1 on djw(a,b,c) speeds up the searches select from djw1 where a = "1"; select from djw1 where a = "1" and b = "2"; select from djw1 where a = "1" and b = "2" and c = "3"; Remember though you must include the initials n columns of the index in your search. The index is only useful when the first column of the index is part of your where clause. Otherwise the index cannot be used to restrict the number of rows returned. Informix will have to read the whole index to find the matching rows. >Our database was created with lots of composite indexes. > >Regards >Dirk -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care