From: carsten severin (cseverinvub.de)
Date: Fri Jun 24 2005 - 04:50:27 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

ok, it sound reasonable to trust the database to choose the best strategy
(and the best indices).

but there are still things i do not understand:

first, i used the optimizer statistics-option for that table (orderline).
then, i created two indices:

create index orderline1 on orderline(type,bzl1,orderlocal)
create index orderline2 on orderline(orderlocal)

then, i tried out following the select-statement:

explain select b.oid from orderline b where b.type=0 and b.bzl1 = 'a' and
b.orderlocal in(0);

this results in an EQUAL CONDITION FOR INDEX-strategy on index orderline1
with a pagecount of 2298 pages, which seems to be quite high to me.

then, i remove the index orderline1. again, i use the same select-statement
as above. with an EQUAL CONDITION FOR INDEX-strategy on index orderline2, i
get a cost of 678 pages, a value that is still quite high, but much smaller
that 2298!

there are two indices. according to the explain-output, the system chooses
the index with a higher pagecount (=the less performant??).

how can i optimize my statements then? creating indices by using the
pagecount-values?

thank you for your help.

Carsten

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]