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 -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
