From df3e1433ea4850e57cade871ced1b0eab4fd785a Mon Sep 17 00:00:00 2001 From: Alex Ingram Date: Fri, 3 Mar 2017 10:34:23 -0600 Subject: [PATCH] Improve PostgreSQL performance for unread message fetch method, too. --- src/core/SQL/PostgreSQL/20/select_messages.sql | 2 +- src/core/SQL/PostgreSQL/20/select_messagesNewerThan.sql | 4 +++- 2 files changed, 4 insertions(+), 2 deletions(-) diff --git a/src/core/SQL/PostgreSQL/20/select_messages.sql b/src/core/SQL/PostgreSQL/20/select_messages.sql index 5bf6a249..50be2e31 100644 --- a/src/core/SQL/PostgreSQL/20/select_messages.sql +++ b/src/core/SQL/PostgreSQL/20/select_messages.sql @@ -2,6 +2,6 @@ SELECT messageid, time, type, flags, sender, message FROM backlog JOIN sender ON backlog.senderid = sender.senderid WHERE bufferid = $1 -AND messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = $1) +AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = $1) ORDER BY messageid DESC LIMIT $2 diff --git a/src/core/SQL/PostgreSQL/20/select_messagesNewerThan.sql b/src/core/SQL/PostgreSQL/20/select_messagesNewerThan.sql index 393d3cfa..d5c01dcd 100644 --- a/src/core/SQL/PostgreSQL/20/select_messagesNewerThan.sql +++ b/src/core/SQL/PostgreSQL/20/select_messagesNewerThan.sql @@ -1,6 +1,8 @@ SELECT messageid, time, type, flags, sender, message FROM backlog LEFT JOIN sender ON backlog.senderid = sender.senderid -WHERE backlog.messageid >= $1 AND bufferid = $2 +WHERE backlog.messageid >= $1 +AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = $1) +AND bufferid = $2 ORDER BY messageid DESC LIMIT $3 \ No newline at end of file -- 2.20.1