From mboxrd@z Thu Jan 1 00:00:00 1970 Received: from krisdoz.my.domain (schwarze@localhost [127.0.0.1]) by krisdoz.my.domain (8.14.5/8.14.5) with ESMTP id s3GIxiYA016648 for ; Wed, 16 Apr 2014 14:59:45 -0400 (EDT) Received: (from schwarze@localhost) by krisdoz.my.domain (8.14.5/8.14.3/Submit) id s3GIxh0L028853; Wed, 16 Apr 2014 14:59:43 -0400 (EDT) Date: Wed, 16 Apr 2014 14:59:43 -0400 (EDT) Message-Id: <201404161859.s3GIxh0L028853@krisdoz.my.domain> X-Mailinglist: mdocml-source Reply-To: source@mdocml.bsd.lv MIME-Version: 1.0 From: schwarze@mdocml.bsd.lv To: source@mdocml.bsd.lv Subject: mdocml: Give the mlinks and keys tables a pageid index, as suggested by X-Mailer: activitymail 1.26, http://search.cpan.org/dist/activitymail/ Content-Type: text/plain; charset=utf-8 Log Message: ----------- Give the mlinks and keys tables a pageid index, as suggested by jeremy@ and espie@. The mlinks index speeds up basic apropos(1) searches by around 30% because it speeds up the final SELECT FROM mlinks query by about 95%. For large result sets, the overall speedup gets even larger, in the extreme case of "apropos Nd~." bymore than 90%. The keys index finally makes the apropos(1) -O option usable: It no longer incurs relevant extra cost, while in the past it was embarrassingly slow. This comes at a cost: Total database build times grow by about 5%, and each index adds about 10% database size with -Q. I consider that acceptable in view of the huge apropos(1) performance gains. The -Q database for /usr/share/man still remains below 1 MB. Modified Files: -------------- mdocml: mandocdb.c Revision Data ------------- Index: mandocdb.c =================================================================== RCS file: /usr/vhosts/mdocml.bsd.lv/cvs/mdocml/mandocdb.c,v retrieving revision 1.135 retrieving revision 1.136 diff -Lmandocdb.c -Lmandocdb.c -u -p -r1.135 -r1.136 --- mandocdb.c +++ mandocdb.c @@ -2182,6 +2182,7 @@ create_tables: " \"pageid\" INTEGER NOT NULL REFERENCES mpages(id) " "ON DELETE CASCADE\n" ");\n" + "CREATE INDEX mlinks_pageid_idx ON mlinks (pageid);\n" "\n" "CREATE TABLE \"names\" (\n" " \"bits\" INTEGER NOT NULL,\n" @@ -2195,7 +2196,8 @@ create_tables: " \"key\" TEXT NOT NULL,\n" " \"pageid\" INTEGER NOT NULL REFERENCES mpages(id) " "ON DELETE CASCADE\n" - ");\n"; + ");\n" + "CREATE INDEX keys_pageid_idx ON keys (pageid);\n"; if (SQLITE_OK != sqlite3_exec(db, sql, NULL, NULL, NULL)) { exitcode = (int)MANDOCLEVEL_SYSERR; -- To unsubscribe send an email to source+unsubscribe@mdocml.bsd.lv