[geary: 1/2] Fix query performance regression brought in with !556

[Michael Gratton <mjog src gnome org>]

commit cea89ff9ec05cd2afbe480f234b8ed75c8775bfa
Author: Chris Heywood <15127-creywood users noreply gitlab gnome org>
Date:   Sat Sep 5 10:02:53 2020 +1000

    Fix query performance regression brought in with !556
    
    The join used to determine how many messages should be left in the
    folder was inefficient. The following query has also been performance
    tested and found to be more efficient as a join (instead of a subquery).

 src/engine/imap-db/imap-db-folder.vala | 19 +++++++++++--------
 1 file changed, 11 insertions(+), 8 deletions(-)
---
diff --git a/src/engine/imap-db/imap-db-folder.vala b/src/engine/imap-db/imap-db-folder.vala
index de015f5a5..4b015cd17 100644
--- a/src/engine/imap-db/imap-db-folder.vala
+++ b/src/engine/imap-db/imap-db-folder.vala
@@ -943,17 +943,20 @@ private class Geary.ImapDB.Folder : BaseObject, Geary.ReferenceSemantics {
         Gee.ArrayList<string> deleted_primary_keys = null;
 
         yield db.exec_transaction_async(Db.TransactionType.RO, (cx) => {
-            // MessageLocationTable.ordering isn't relied on due to IMAP folder
-            // UIDs not guaranteed to be in order.
+            // MessageLocationTable.ordering isn't relied on throughout due 
+            // to IMAP folder UIDs not guaranteed to be in order. Make sure to 
+            // performance test any changes to the select queries below.
             StringBuilder sql = new StringBuilder();
             sql.append("""
                 SELECT COUNT(*)
-                FROM MessageLocationTable ml
-                INNER JOIN MessageTable m
-                INDEXED BY MessageTableInternalDateTimeTIndex
-                    ON ml.message_id = m.id
-                WHERE ml.folder_id = ?
-                AND m.internaldate_time_t >= ?
+                FROM MessageLocationTable
+                WHERE folder_id = ?
+                AND message_id IN (
+                    SELECT id
+                    FROM MessageTable
+                    INDEXED BY MessageTableInternalDateTimeTIndex
+                    WHERE internaldate_time_t >= ?
+                )
             """);
 
             Db.Statement stmt = cx.prepare(sql.str);