NetNews Usenet Archive 1992 #30/NN_1992_30.iso/spool/comp/database/informix/2687

Internet Message Format | 1992-12-14 | 2.6 KB

Path: sparky!uunet!gumby!wupost!emory!emory!not-for-mail
From: [e-mail] (Jim Gordon)
Newsgroups: comp.databases.informix
Subject: Re: Two index query optimization problem
Date: 14 Dec 1992 12:58:07 -0500
Organization: Mailing List Gateway
Lines: 47
Sender: [e-mail]
Distribution: world
Message-ID: <[e-mail]>
Reply-To: [e-mail] (Jim Gordon)
NNTP-Posting-Host: emory.mathcs.emory.edu
X-Informix-List-ID: <list.1697>

> 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: [e-mail]
Company:  DHL Systems Inc          Phone:    [phone] (Work)
Address:  1700 S. Amphlett Blvd.             [phone] (Home)
          San Mateo, CA 94402      Fax:      [phone]
--------------------------------------------------------------------