RE: Usage of Indices

I would agree with the developer. If they do an order by it is not possible to use two single key indexes to do an order by on both of thosee keys. Example select a,b order by a,b a b 1|1 1|2 2|1 2|3 2|4 3|1 3|6 The index on b would not be helpful in the order by a,b. You can do an index on b, and an index on a,b and do without an index on a. The database is smart enough to just look at the first part of the index even if you didnt specify the second. Hope this helps Will >===== Original Message From richard.spitz@ana.med.uni-muenchen.de ===== >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 | >+--------------------------+------------------------------------------+ ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------