Re: Two index query optimization problem

> I'm having some problems with Informix over optimizing my query. > I have a table with > 500000 rows and I can't get Informix to > use what I deem to be the "correct" index. > > There are two indexes on the table: > > index1: unique index on A, B; > index2: unique index on C, D, A, B; > > The problem I'm having is that I can't run a query with A and B mentioned > in the WHERE clause that will use index2. I suspect that the optimizer > is looking at the query and saying; hey, I got A, I got B, I've got a unique > index1 on both A and B. Look no further, I am set dudes and dudettes, and > I am outta here! (optimizers talk like that you know). > Devin, I have had a similar problem in the past in an early version of 4.0 and I found something quite interesting. Though Informix will tell you that the new optimiser checks every path for completing the query in fact it does not check every index. It always uses the first one it finds that can help do the query and will ignore later indexes which are more appropriate. So our solution was to drop both indexes and build them again but in the opposite order (Index2 followed by Index1). Now I have not tested this in 4.1 or 5.0 so I cannot say whether this solution will still work for you. Alternatively you can change the optimisation level. It changes for your backend process until you change it back. It does not change for everybody or for ever. So you can set it to low before the select statement and set it back to high afterwards. Lastly by splitting the select statement into two or possibly by doing a sub-query you may also change the choice of index. This can have performance impacts, these may not necessarily be negative, there are times when splitting queries up, using cursors, etc can speed up response. 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 --------------------------------------------------------------------