Usage of Indices
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". 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"? Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. med Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | | Klinikum Grosshadern | FAX : +49-89-7095-6420 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+
You need to keep the index. However, I can see no reason to keep the "adat" index since the "datnr" index can be used. 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". > > 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"? > > Regards, Richard > -- > +--------------------------+------------------------------------------+ > | Dr. med Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | > | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | > | Klinikum Grosshadern | FAX : +49-89-7095-6420 | > | 81366 Munich, Germany | GSM : +49-172-8933578 | > +--------------------------+------------------------------------------+