lei import: speed up repeated Maildir imports

[public-inbox.git] / lib / PublicInbox / LeiMailSync.pm

diff --git a/lib/PublicInbox/LeiMailSync.pm b/lib/PublicInbox/LeiMailSync.pm
index 22ee1109cc7e13ba9681fb08fe12c5c35e43dce7..ec05404ad004fc9740e7aec781fd9ddc96efd539 100644 (file)
--- a/lib/PublicInbox/LeiMailSync.pm
+++ b/lib/PublicInbox/LeiMailSync.pm
@@ -54,6 +54,10 @@ CREATE TABLE IF NOT EXISTS blob2num (
        UNIQUE (oidbin, fid, uid)
 )
 
+       # speeds up LeiImport->ck_update_kw (for "lei import") by 5-6x:
+       $dbh->do(<<'');
+CREATE INDEX IF NOT EXISTS idx_fid_uid ON blob2num(fid,uid)
+
        $dbh->do(<<'');
 CREATE TABLE IF NOT EXISTS blob2name (
        oidbin VARBINARY NOT NULL,
@@ -62,6 +66,10 @@ CREATE TABLE IF NOT EXISTS blob2name (
        UNIQUE (oidbin, fid, name)
 )
 
+       # speeds up LeiImport->pmdir_cb (for "lei import") by ~6x:
+       $dbh->do(<<'');
+CREATE INDEX IF NOT EXISTS idx_fid_name ON blob2name(fid,name)
+
 }
 
 sub fid_for {
@@ -361,15 +369,24 @@ sub forget_folder {
        $dbh->do('DELETE FROM folders WHERE fid = ?', undef, $fid);
 }
 
-sub imap_oid2 ($$$) {
-       my ($self, $uri, $uid) = @_; # $uri MUST have UIDVALIDITY
-       my $fid = $self->{fmap}->{"$uri"} //= fid_for($self, "$uri") // return;
+sub imap_oidbin ($$$) {
+       my ($self, $url, $uid) = @_; # $url MUST have UIDVALIDITY
+       my $fid = $self->{fmap}->{$url} //= fid_for($self, $url) // return;
        my $sth = $self->{dbh}->prepare_cached(<<EOM, undef, 1);
 SELECT oidbin FROM blob2num WHERE fid = ? AND uid = ?
 EOM
        $sth->execute($fid, $uid);
-       my ($oidbin) = $sth->fetchrow_array;
-       $oidbin ? unpack('H*', $oidbin) : undef;
+       $sth->fetchrow_array;
+}
+
+sub name_oidbin ($$$) {
+       my ($self, $mdir, $nm) = @_;
+       my $fid = $self->{fmap}->{$mdir} //= fid_for($self, $mdir) // return;
+       my $sth = $self->{dbh}->prepare_cached(<<EOM, undef, 1);
+SELECT oidbin FROM blob2name WHERE fid = ? AND name = ?
+EOM
+       $sth->execute($fid, $nm);
+       $sth->fetchrow_array;
 }
 
 sub imap_oid {
@@ -384,10 +401,10 @@ sub imap_oid {
                }
                $lei->qerr(@{$err->{qerr}}) if $err->{qerr};
        }
-       imap_oid2($self, $folders->[0], $uid_uri->uid);
+       my $oidbin = imap_oidbin($self, $folders->[0], $uid_uri->uid);
+       $oidbin ? unpack('H*', $oidbin) : undef;
 }
 
-
 # FIXME: something with "lei <up|q>" is causing uncommitted transaction
 # warnings, not sure what...
 sub DESTROY {