Re: Why composite indexes ?

Spock wrote: > > 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 ? > Our database was created with lots of composite indexes. > > Regards > Dirk Can we have some SQL to see exactly what is happening? Composite indexes are ordered. So far as I know, create index a on b(c,d); is not the same as create index a on b(d,c); Doesn't the optimiser use a composite index if the search is on a set of the first columns in the index? It can't use the index if the search excludes the first columns in the index. Anyone got any evidence to back up these musings based on vague recollections of set explain output? -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 Mail: Peter.Lancashire.PL1@bayer.co.uk --- My Internet plumbing does not allow me to mail and post news together. Sorry. All opinions are my own and not those of Bayer plc. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/