Re: [MonetDB-users] Why does MonetDB take indexes not into account?

On Nov 26, 2007 9:14 AM, Stefan Manegold wrote:

...

On Mon, Nov 26, 2007 at 08:41:31AM +0100, Markus Gritsch wrote:

...

Yes, this would be the case in an ideal world :) Nevertheless the system could take some hints from the schema designer into account (via indexes), in case he exactly knows how the schema is queried and what columns can benefit greatly from having an indexed on them.

However, if the cost of maintaining the indices under updates exceeds the (potential) benefits of exploiting the indices for queries, the investment is debatable.

True. But there are cases (as in mine) where the cost of maintaining the indices does not exceed the benefit of having indexes for queries. Having fast queries without specifying an index would of course be ideal, so I would love to see MonetDB achieving this.

...

Or, in other words, even without "old-fashioned" indices, MonetDB/SQL appears to outperform MySQL by more than a factor 3 (in your case).

Please look at my e-mail again. MonetDB outperforms MySQL in my case only when *not* using the index i.e. querying over the "entries" of *all* "acts", which is very good. Unfortunately, as already posted in my original e-mail, in the common case (in my application) of querying only over the "entries" of the *open* "acts", MonetDB is 3x slower than MySQL. But even then its not the index (or lack of it) causing the slowdown. It the optimizer taking the in efficient route. With or without

On Mon, Nov 26, 2007 at 09:37:48AM +0100, Markus Gritsch wrote: the index it should have taken the route via your 'is_closed = 0'. I will look into this problem, but changing the optimizer is always with great causion as it may cause unwanted side effects (performance degredation) on other queries. Niels

Markus

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl