From c4a4e9809ddd10f2094e1b088728101ace89297f Mon Sep 17 00:00:00 2001 From: Eric Wong Date: Mon, 11 Oct 2021 08:06:15 +0000 Subject: extindex: speed up --reindex --fast This required some tweaking of xref3 indices in over.sqlite3, but the end result is it brings no-op "--reindex --fast --all" checks down to roughly 20 minutes (from 30-40 minutes) on lore/all. This is faster because a bunch of small SQLite queries are still slower en-mass than a bunch of perlops. Despite the lack of IPC overhead, crossing .so boundaries and repeating lookups over btrees is still slower than doing the same with Perl hash tables. --- lib/PublicInbox/OverIdx.pm | 10 +++++++--- 1 file changed, 7 insertions(+), 3 deletions(-) (limited to 'lib/PublicInbox/OverIdx.pm') diff --git a/lib/PublicInbox/OverIdx.pm b/lib/PublicInbox/OverIdx.pm index 985abbf4..46f7a066 100644 --- a/lib/PublicInbox/OverIdx.pm +++ b/lib/PublicInbox/OverIdx.pm @@ -543,9 +543,13 @@ CREATE TABLE IF NOT EXISTS xref3 ( $dbh->do('CREATE INDEX IF NOT EXISTS idx_docid ON xref3 (docid)'); # performance critical, this is not UNIQUE since we may need to - # tolerate some old bugs from indexing mirrors - $dbh->do('CREATE INDEX IF NOT EXISTS idx_nntp ON '. - 'xref3 (oidbin,xnum,ibx_id)'); + # tolerate some old bugs from indexing mirrors. n.b. we used + # to index oidbin here, but leaving it out speeds up reindexing + # and "XHDR Xref <$MSGID>" isn't any slower w/o oidbin + $dbh->do('CREATE INDEX IF NOT EXISTS idx_reindex ON '. + 'xref3 (xnum,ibx_id)'); + + $dbh->do('CREATE INDEX IF NOT EXISTS idx_oidbin ON xref3 (oidbin)'); $dbh->do(<<''); CREATE TABLE IF NOT EXISTS eidx_meta ( -- cgit v1.2.3-24-ge0c7