At 11:48 AM 19-04-02 +1200, you wrote:

>I have a table with two indices
>
>index1 on fieldA, fieldB
>index2 on fieldA, fieldC
>
>fieldB is varchar (20) and fieldC is numeric (18, 0)
>
>when I do a select statement like
>
>select * from tableA
>where fieldA = :val1 and
> fieldC = :val2
>
>I expect it to use index2, but for some reason it uses index1. I need the
>statement to use index2 as I'm using them in stor proc, and this
>behaviour makes stor proc too slow.

You are encountering that ol' problem of overlapping indexes. The solution
is supposed to be "force the optimizer to use an explicit plan" but, in
practice, it's not always perfect, ofttimes not even worthwhile if any of
the indexes involved are system-generated - your plan will become invalid
in the event you have to restore from backup.

What's commonly done is to disable the "usefulness" the optimizer perceives
in using index1, e.g. by including a fatuous WHERE criterion involving the
outlying column of the unwanted index....so, in this case, adding
...
WHERE
...
and fieldB = fieldB

should do it. It's quite fun to play about with stuff like this in IB_SQL
and see the different plans that the optimizer produces. When you see one
that looks good to you, go with that statement for your app.

Another alternative is not to define overlapping indexes if there is a way
to avoid it. For example, with individual indexes on each of fieldA.,
fieldB and fieldC, the individual indexes on A and C would get used for
your example and the one on fieldB would be ignored; or one on
fieldA-fieldC and one on fieldB would cover the possibilities.

And of course there is that ol' warhorse - don't define *any* indexes that
duplicate the auto indexes the database creates for constraints.

Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________