From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on dcvr.yhbt.net X-Spam-Level: X-Spam-Status: No, score=-4.0 required=3.0 tests=ALL_TRUSTED,BAYES_00 shortcircuit=no autolearn=ham autolearn_force=no version=3.4.2 Received: from localhost (dcvr.yhbt.net [127.0.0.1]) by dcvr.yhbt.net (Postfix) with ESMTP id 7E92D1F4B4 for ; Sat, 5 Dec 2020 22:59:31 +0000 (UTC) From: Eric Wong To: meta@public-inbox.org Subject: [PATCH] overidx: {num} column is INTEGER PRIMARY KEY Date: Sat, 5 Dec 2020 22:59:31 +0000 Message-Id: <20201205225931.23906-1-e@80x24.org> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit List-Id: 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)');