Bernd Eckenfels wrote (order of citation changed):
> Holger Becker wrote:
>
> > Christof Lehmann wrote:
> >
> > > I'm a newbe in working with indices on maxdb. i have a large
tables
> > > orderlocal with about 200.000 lines (not that large though, isn't
> > > it?).
> > >
> > > i create the following indices (order of creating):
> > >
> > > create index ol1 on orderline(bzl1,orderlocal) create index ol2 on
> > > orderline(bzl2,orderlocal) create index ol3 on
> > > orderline(bzl1,bzl2,orderlocal)
> > >
> > > i try to analyse the following query:
> > >
> > > explain select * from orderline b where b.bzl1='a' and b.bzl2 =
'b'
> > > and
> > > b.orderlocal in(0)
> > >
> > > Result: STRATEGY EQUAL CONDITION FOR INDEX, Pagecount=2133.
> > >
> > > the analyser says, index ol1 is used. why? doesn't suite index ol3
> > > much more to the query-statement? How does the optimizer
> > > selects the
> > > indices??
> > >
> > > our problem is, that we join the result over a subselect
> > > (included in
> > > the oderlocal in()-term) with another 200.000 rows-table and this
> > > leads to an uncredibly high load on our database-server!
> > >
> > > Thank you for your help,
> > >
> > > Carsten
> >
> > Hi,
> >
> > MaxDB uses a cost based optimizer.
> > This means that for every possible strategy a cost value is
calculated
> > (mainly based on I/O costs) and the strategy with the lowest
> > cost value
> > is choosen.
> > Because ol3 includes 3 columns the index migth be larger than ol1.
> > So if bzl2 is not very distinct the read costs on ol1 might be lower
> > than on ol3 and the optimizer choose index ol1.
> >
> > For your join problem please post the explain output here perhaps we
> > find a solution to speed it up.
> >
> > Kind regards
> > Holger
>
> Hello,
>
> I wonder if it is needed maxdb to have overlapping index
> definitions. If
> I have only the ol3 index, it would also be used for the query agains
> some of ist columns, right? This would speed up insert/update if there
> are no 3 index definitions for one table.
>
> Is the cost based optimizer taking into account if the index
> pages are
> in cache?
>
> Gruss
> Bernd
Hi,
it depends.
If you have different commands with differents search conditions
or different order by clause and your performance without index is
to bad overlapping indexes migth help.
Example:
select * from orderline where bzl1 = ? and orderlocal = ?
-> could use ol1 or ol3 but on ol3 only "bzl1 = ?"
would be used effectively
select * from orderline where bzl2 = ?
-> could only use ol2
select * from orderline where orderlocal = ?
order by bzl1, bzl2, orderlocal
-> ol3 supports the order by clause and no additional
ordering is needed
The optimizer doesn't knows what's in cache and what's not.
So he couldn't take it into account.
Kind regards
Holger
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
