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.

1 files changed, 3 insertions, 1 deletions

diff --git a/mandocdb.c b/mandocdb.c
index cef53214..aae90678 100644
--- a/mandocdb.c
+++ b/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;