Re: Two index query optimization problem

Devin Wala writes: > > >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). > >This would be fine if my queries were of the form: > > WHERE C=value AND D=value A=value AND B=value; > >However, one of my queries is of the form: > > WHERE C=value AND D=value A>value AND B=value; > >so it doesn't _really_ have a value for A. I do need to have both >indexes for performance reasons. I don't think that setting optimization >to low is an option in this case since it effects the whole database >back end and not just the single query. We are running Online V 4.10.UF2, >SQL V 4.00.UG2 on a Pyramid MI Server. > >Does anyone have any ideas, suggestions, or comments? Any help you >can provide is greatly appreciated. Of course the optimizer is choosing the first index, as it will certainly be more selective than the second index. Given that you have a > clause, the second index could only be used for positioning to the beginning of the potential set. Added to this the fact that you will only be able to use 3/4 of the 4 part index (once you hit the >, it can't use any part beyond that) and the first index, being unique and having both parts available as literals in the query, is preferable. You could try changing the second index to be C,D,B,A, which would allow it to use all 4 parts of the index. The optimizer may still decide the first one is better. Make sure you run UPDATE STATISTICS so that the optimizer has recent stats to do its analysis with. If you still see the other index used, and are convinced that the first would be better, try dropping the first index and timing the query (which would be using the second index for sure). Also note the cost and estimated rows returned from sqexplain.out. Now recreate the first index and do the same thing again. The purpose of this exercise is to see if your suspicions are correct (i.e. that the second index would be "better"). Quite often, I've found that what I thought would be a "better" index was not so, and that the optimizer actually made the better choice. Dave -- Disclaimer: These opinions are not those of Informix Software, Inc. ************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney