Re: Indexing strategy

->From: spitz@ana.med.uni-muenchen.de (Richard Spitz) ->Subject: Indexing strategy ->Date: Tue, 27 Apr 1993 11:04:34 GMT ->Reply-To: spitz@ana.med.uni-muenchen.de (Richard Spitz) ->Organization: Inst. f. Anaesthesiologie der LMU, Muenchen (Germany) -> ... omitted ... -> ->I have several large tables under ONLINE 4.0 (soon to be upgraded to 4.1), ->whose structure is similar to: -> -> patient_nr INTEGER, -> procedure_nr INTEGER, -> proc_date DATE, -> result SMALLFLOAT -> ->There can be only one procedure of the same type per day for one patient, ->so the primary key is composed of patient_nr, procedure_nr and proc_date, ->and I defined a composite unique index on those three columns. Since there ->will be many queries on a per-patient, per-procedure or per-date basis, ->I also defined duplicate indices on each one of these rows. -> ->Performance is important, ... ... omitted ... -> ->The question is: Will INFORMIX be able to use the composite index to query ->columns that are part of it with the same speed as with the additional ->indices that consist only of the specific column? -> ->Thanks for any help, -> ->Richard ->-- ->+----------------------------+-------------------------------------------+ ->| Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | ->| EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3421 | ->| Klinikum Grosshadern | FAX : +49-89-7095-8886 | ->| Munich, Germany | | ->+----------------------------+-------------------------------------------+ If you CREATE UNIQUE INDEX idx3 ON tab ( patient_nr, procedure_nr, proc_date ) then you do not need indexes on ( partient_nr ) or ( patient_nr, procedure_nr ) for Informix to be able to use indexes in resolving queries on those columns. In other words, Informix can use the LEADING columns in a composite key index as an index while doing queries. However, middle or trailing columns in a composite index can not be used in this way. Thus, if you need to query on procedures performed on a given date, regardless of patient, then you could gain performance by having a ( procedure_nr, proc_date ) index. Will it query as fast using only part of a composite index as using a specially crafted index? Probably. If you define the unique 3-part index before the non-unique 2-part index, the query optimizer may already be using the 3-part index (so I have heard), since the optimizer finds the 3-part index first, and it will do the job. Regards, Alan +------------------------------+---------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | Martin Marietta, LSC | ( Please note: My opinions do not ) | | P.O. Box 179, M/S 5422 | ( represent official Martin policy. ) | | Denver, Colorado 80201-0179 | Voice: 303-977-9998 | +------------------------------+---------------------------------------+