On Mon, Nov 26, 2007 at 07:53:21AM +0100, Martin Kersten wrote:
Markus Gritsch wrote:
Hi,
I basically have this schema:
CREATE TABLE "act" ( "id" int NOT NULL auto_increment, "title" text NOT NULL, "is_closed" bool NOT NULL, PRIMARY KEY ("id") ); CREATE INDEX act_isClosedIndex ON act (is_closed);
CREATE TABLE "entry" ( "id" int NOT NULL auto_increment, "note" text NOT NULL, "act_id" int NOT NULL, PRIMARY KEY ("id"), CONSTRAINT "entry_act_id_exists" FOREIGN KEY ("act_id") REFERENCES "act" ("id") ); CREATE INDEX entry_actIndex ON entry (act_id);
Each "act" has several "entries" and each "entry" belongs to one "act".
*) full-text search in the "note" column over all entries:
SELECT * FROM entry WHERE entry.note LIKE ('%hui%')
MySQL: 4.5 Seconds MonetDB: 0.8 Seconds
So this is very good and this motivates me to switch to MonetDB. However, the majority of the acts have the "is_closed" field set. The usual case in my application is to take into account only the entries of open acts:
*) full-text search in the "note" column over all entries of the open acts:
SELECT * FROM entry, act WHERE entry.act_id = act.id AND act.is_closed = 0 AND entry.note LIKE ('%hui%')
MySQL: 0.26 Seconds MonetDB: 0.8 Seconds As the time is remarkably close to the earlier It looks like monet does the 'LIKE' before the 'is_closed = 0'. Looking at the 'explain' output indeed shows this too. We could improve the optimizer for these cases.
Niels
Here, MySQL can take advantage of the index on the is_closed column, whereas MonetDB does not. Is this intended? Databases without indexes seem strange to me.
it has little to do with the indices, but more with the way the SQL optimizer produces a plan is my impression. Perhaps Niels sees an opportunity or missed opportunity for the plan generator.
MonetDB internally uses (hash-)indices, which are created for the duration of the session. And the holy grale for database designers is to create a self-organizing system, one that learns how to index without user interaction.
In this example, it would be interesting to see what a second call to the same query produces.
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
------------------------------------------------------------------------- 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