From cbc57790069f3bc3e54c59e54cd3274abff536e6 Mon Sep 17 00:00:00 2001 From: Shane Synan Date: Sat, 4 Mar 2017 15:09:14 -0600 Subject: [PATCH] Add lastmsgid limit on fetching backlog to SQLite Copy lastmsgid fetch limit improvements from PostgreSQL to SQLite, improving performance by not requiring scanning from the start of the backlog table to the present. This builds upon pull request #273, including the original intent of improving performance for unread backlog fetching. The original pull request left out these lines when applying the PostgreSQL changes to SQLite. NOTE: The client-side generating these invalid values should be fixed in future commits. --- src/core/SQL/SQLite/21/select_messagesNewerThan.sql | 5 +++-- src/core/SQL/SQLite/21/select_messagesNewestK.sql | 1 + 2 files changed, 4 insertions(+), 2 deletions(-) diff --git a/src/core/SQL/SQLite/21/select_messagesNewerThan.sql b/src/core/SQL/SQLite/21/select_messagesNewerThan.sql index 69d948d6..bac2a047 100644 --- a/src/core/SQL/SQLite/21/select_messagesNewerThan.sql +++ b/src/core/SQL/SQLite/21/select_messagesNewerThan.sql @@ -1,7 +1,8 @@ SELECT messageid, time, type, flags, sender, message FROM backlog JOIN sender ON backlog.senderid = sender.senderid -WHERE bufferid = :bufferid - AND backlog.messageid >= :firstmsg +WHERE backlog.messageid >= :firstmsg + AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferid) + AND bufferid = :bufferid ORDER BY messageid DESC LIMIT :limit diff --git a/src/core/SQL/SQLite/21/select_messagesNewestK.sql b/src/core/SQL/SQLite/21/select_messagesNewestK.sql index 6cc81cc5..6f86e70b 100644 --- a/src/core/SQL/SQLite/21/select_messagesNewestK.sql +++ b/src/core/SQL/SQLite/21/select_messagesNewestK.sql @@ -2,5 +2,6 @@ SELECT messageid, time, type, flags, sender, message FROM backlog JOIN sender ON backlog.senderid = sender.senderid WHERE bufferid = :bufferid +AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferid) ORDER BY messageid DESC LIMIT :limit -- 2.20.1