From 0dceebd0a85774c92af247e6da5e2f5a0ee8417c Mon Sep 17 00:00:00 2001 From: "Eric Wong (Contractor, The Linux Foundation)" Date: Tue, 3 Apr 2018 11:09:10 +0000 Subject: mbox: remove remaining OFFSET usage in SQLite We can use id_batch in the common case to speed up full mbox retrievals. Gigantic msets are still a problem, but will be fixed in future commits. --- lib/PublicInbox/Over.pm | 13 ++++++------- 1 file changed, 6 insertions(+), 7 deletions(-) (limited to 'lib/PublicInbox/Over.pm') diff --git a/lib/PublicInbox/Over.pm b/lib/PublicInbox/Over.pm index b230d44a..0bd6008b 100644 --- a/lib/PublicInbox/Over.pm +++ b/lib/PublicInbox/Over.pm @@ -50,9 +50,7 @@ sub do_get { my ($self, $sql, $opts, @args) = @_; my $dbh = $self->connect; my $lim = (($opts->{limit} || 0) + 0) || 1000; - my $off = (($opts->{offset} || 0) + 0) || 0; $sql .= "LIMIT $lim"; - $sql .= " OFFSET $off" if $off > 0; my $msgs = $dbh->selectall_arrayref($sql, { Slice => {} }, @args); load_from_row($_) for @$msgs; $msgs @@ -77,7 +75,7 @@ ORDER BY num ASC sub nothing () { wantarray ? (0, []) : [] }; sub get_thread { - my ($self, $mid, $opts) = @_; + my ($self, $mid, $prev) = @_; my $dbh = $self->connect; my $id = $dbh->selectrow_array(<<'', undef, $mid); @@ -96,13 +94,14 @@ SELECT tid,sid FROM over WHERE num = ? LIMIT 1 defined $tid or return nothing; # $sid may be undef - my $cond = 'FROM over WHERE (tid = ? OR sid = ?) AND num > 0'; - my $msgs = do_get($self, <<"", $opts, $tid, $sid); -SELECT * $cond ORDER BY ts ASC + $prev ||= 0; + my $cond = 'FROM over WHERE (tid = ? OR sid = ?) AND num > ?'; + my $msgs = do_get($self, <<"", {}, $tid, $sid, $prev); +SELECT * $cond ORDER BY num ASC return $msgs unless wantarray; - my $nr = $dbh->selectrow_array(<<"", undef, $tid, $sid); + my $nr = $dbh->selectrow_array(<<"", undef, $tid, $sid, $prev); SELECT COUNT(num) $cond ($nr, $msgs); -- cgit v1.2.3-24-ge0c7