Re: Indexing strategy

> > 1rk2ciINNrhr@emory.mathcs.emory.edu writes: > >: > Stuff deleted.. > > >: 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. > >: > > Is this true? Can some one with the source confirm this? > Exactly how does it decide which index to use? > > Regards, Karl Funk. > This may have come from one of my comments. Back when I was using Online V4.0 UA1 I discovered that the optimiser selected indexes on the basis of looking through sysindexes in the order in which the indexes were created. It would find and use the first index which matched the first part of the required filter even if another index had all the parts in the correct order and the first index had unrelated parts in it. This caused us some problems as what we had was an order by clause which was intended to use the other index but the filter statement which filtered on the first part of the index forced the selection of the first index found causing temporary tables to be created for sorting. We got round it by re-creating the indexes in the other order. Having never hit on this requirement since and having left the company where the above software is running, I cannot say whether the much improved optimisers in 4.1 and 5.0 still have this problem. Actually I doubt it as I did report the problem to Informix when it occurred. Cheers - Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 (Work) Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home) San Mateo, CA 94402 Fax: (415) 571-6429 --------------------------------------------------------------------