From: davek@informix.com (David Kosenko)
Newsgroups: comp.databases.informix
Subject: Re: Two index query optimization problem
Date: 15 Dec 92 16:49:49 GMT
Distribution: usa
Organization: Informix Software, Inc.

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
Newsgroups: comp.databases.informix