diff options
author | Eric Wong (Contractor, The Linux Foundation) <e@80x24.org> | 2018-04-03 11:09:08 +0000 |
---|---|---|
committer | Eric Wong (Contractor, The Linux Foundation) <e@80x24.org> | 2018-04-03 12:06:11 +0000 |
commit | f91753f00b760c0e06eb0384792e5b18bd99bb0f (patch) | |
tree | 2cc2f60b629b4947fcd4a02c39c7cfc688c2c243 /lib/PublicInbox/Over.pm | |
parent | e932bd748750234331d5664873272e0cb7ba6025 (diff) | |
download | public-inbox-f91753f00b760c0e06eb0384792e5b18bd99bb0f.tar.gz |
While SQLite is faster than Xapian for some queries we use, it sucks at handling OFFSET. Fortunately, we do not need offsets when retrieving sorted results and can bake it into the query. For inbox.comp.version-control.git (v1 Xapian), XOVER and XHDR are over 20x faster.
Diffstat (limited to 'lib/PublicInbox/Over.pm')
-rw-r--r-- | lib/PublicInbox/Over.pm | 15 |
1 files changed, 8 insertions, 7 deletions
diff --git a/lib/PublicInbox/Over.pm b/lib/PublicInbox/Over.pm index 3d285ac2..a7fd1315 100644 --- a/lib/PublicInbox/Over.pm +++ b/lib/PublicInbox/Over.pm @@ -51,25 +51,26 @@ sub do_get { my $dbh = $self->connect; my $lim = (($opts->{limit} || 0) + 0) || 1000; my $off = (($opts->{offset} || 0) + 0) || 0; - $sql .= "LIMIT $lim OFFSET $off"; + $sql .= "LIMIT $lim"; + $sql .= " OFFSET $off" if $off > 0; my $msgs = $dbh->selectall_arrayref($sql, { Slice => {} }, @args); load_from_row($_) for @$msgs; $msgs } sub query_xover { - my ($self, $beg, $end, $off) = @_; - do_get($self, <<'', { offset => $off }, $beg, $end); + my ($self, $beg, $end) = @_; + do_get($self, <<'', {}, $beg, $end); SELECT * FROM over WHERE num >= ? AND num <= ? ORDER BY num ASC } sub query_ts { - my ($self, $ts, $opts) = @_; - do_get($self, <<'', $opts, $ts); -SELECT * FROM over WHERE num > 0 AND ts >= ? -ORDER BY ts ASC + my ($self, $ts, $prev) = @_; + do_get($self, <<'', {}, $ts, $prev); +SELECT num,ddd FROM over WHERE ts >= ? AND num > ? +ORDER BY num ASC } |