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
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