Re: Usage of Indices

Richard Spitz wrote: > > Hi Informixers, > > I just stumbled across a table in a database (which is not maintained > by me) which indices defined as such: > > ---------------------------------------------------------------------- > Index name Owner Type Cluster Columns > > pr1 andok91 unique Yes andoknr > > namen andok91 dupls No nachname > vorname > > datnr andok91 dupls No datum > andoknr > > adat andok91 dupls No datum > ---------------------------------------------------------------------- > > I asked the developer why she had single column indices on "andoknr" > and "datum" and also a combined index on both. She insisted that this > was necessary to get good performance for combined "order by" > statements in select, like "select ... order by datum, andoknr". This would certainly use the composite index datnr. > Same reason for the combined index on "nachname, vorname". FYI, the > developer comes from C-ISAM and has just recently re-designed her > application to directly use SQL and IDS 7.30 as backend. > > The table in question has about 350000 rows and grows by about > 50000 rows per year. Is the developer correct, or can I safely > delete the combined index "datnr", and redefine the index "namen" > to only use the column "nachname"? If you are sorting on both datum and andoknr in that order, then no, you cannot remove that index without loss of performance. The optimiser can only use elements of an index from the left. So an index on a,b,c can be used to order on a, or on a,b or on a,b,c. Also of course, it can be used to filter at the same time as ordering. However, you can remove the index adat, because it's function can also be performed by index datnr. A sure way to determine which indexes are being used by a query is to SET EXPLAIN ON. This can sometimes produce a surprise or two. ;-) Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+