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 -----Original Message----- From: Becker, Holger [mailto:[EMAIL PROTECTED] Sent: Thursday, June 23, 2005 3:51 PM To: Christof Lehmann; [email protected] Subject: RE: used index 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]
