[PATCH] overidx: {num} column is INTEGER PRIMARY KEY

[PATCH] overidx: {num} column is INTEGER PRIMARY KEY

[Eric Wong]

INTEGER PRIMARY KEY can be an alias for ROWID in SQLite and is
already unique, so there's no need for a separate UNIQUE(num)
index.

With a smallish ~3K, freshly indexed v2 inbox, this results in a
~40K space savings, reducing over.sqlite3 from 1.375M to 1.335M
(post-VACUUM).

This only affects newly-indexed inboxes; existing DBs will
require manual intervention to take advantage of space savings.

Link: https://www.sqlite.org/rowidtable.html
---
 lib/PublicInbox/OverIdx.pm | 5 ++---
 1 file changed, 2 insertions(+), 3 deletions(-)

diff --git a/lib/PublicInbox/OverIdx.pm b/lib/PublicInbox/OverIdx.pm
index 88daa64f..635aa314 100644
--- a/lib/PublicInbox/OverIdx.pm
+++ b/lib/PublicInbox/OverIdx.pm
@@ -389,13 +389,12 @@ sub create_tables {
 
 	$dbh->do(<<'');
 CREATE TABLE IF NOT EXISTS over (
-	num INTEGER NOT NULL, /* NNTP article number == IMAP UID */
+	num INTEGER PRIMARY KEY NOT NULL, /* NNTP article number == IMAP UID */
 	tid INTEGER NOT NULL, /* THREADID (IMAP REFERENCES threading, JMAP) */
 	sid INTEGER, /* Subject ID (IMAP ORDEREDSUBJECT "threading") */
 	ts INTEGER, /* IMAP INTERNALDATE (Received: header, git commit time) */
 	ds INTEGER, /* RFC-2822 sent Date: header, git author time */
-	ddd VARBINARY, /* doc-data-deflated (->to_doc_data, ->load_from_data) */
-	UNIQUE (num)
+	ddd VARBINARY /* doc-data-deflated (->to_doc_data, ->load_from_data) */
 )
 
 	$dbh->do('CREATE INDEX IF NOT EXISTS idx_tid ON over (tid)');