From a63a6bea52c160a0ae3a17b7dd4931b76f84e5bd Mon Sep 17 00:00:00 2001 From: Ingo Schwarze Date: Wed, 16 Apr 2014 18:59:43 +0000 Subject: 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. --- mandocdb.c | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'mandocdb.c') diff --git a/mandocdb.c b/mandocdb.c index 71f67006..8ad91953 100644 --- a/mandocdb.c +++ b/mandocdb.c @@ -1,4 +1,4 @@ -/* $Id: mandocdb.c,v 1.135 2014/04/13 22:03:04 schwarze Exp $ */ +/* $Id: mandocdb.c,v 1.136 2014/04/16 18:59:43 schwarze Exp $ */ /* * Copyright (c) 2011, 2012 Kristaps Dzonsons * Copyright (c) 2011, 2012, 2013, 2014 Ingo Schwarze @@ -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; -- cgit v1.2.3-56-ge451