Improve content_item db table index

fixes #923

Co-authored-by: eta <eta@theta.eu.org>

1 files changed, 10 insertions, 2 deletions

diff --git a/libdino/src/service/database.vala b/libdino/src/service/database.vala
index 6384fa78..40967025 100644
--- a/libdino/src/service/database.vala
+++ b/libdino/src/service/database.vala
@@ -7,7 +7,7 @@ using Dino.Entities;
 namespace Dino {
 
 public class Database : Qlite.Database {
-    private const int VERSION = 16;
+    private const int VERSION = 17;
 
     public class AccountTable : Table {
         public Column<int> id = new Column.Integer("id") { primary_key = true, auto_increment = true };
@@ -62,7 +62,7 @@ public class Database : Qlite.Database {
         internal ContentItemTable(Database db) {
             base(db, "content_item");
             init({id, conversation_id, time, local_time, content_type, foreign_id, hide});
-            index("contentitem_localtime_counterpart_idx", {local_time, conversation_id});
+            index("contentitem_conversation_hide_localtime_time_idx", {conversation_id, hide, local_time, time});
             unique({content_type, foreign_id}, "IGNORE");
         }
     }
@@ -390,6 +390,14 @@ public class Database : Qlite.Database {
                 error("Failed to upgrade to database version 16: %s", e.message);
             }
         }
+        if (oldVersion < 17) {
+            try {
+                exec("DROP INDEX contentitem_localtime_counterpart_idx");
+                exec("CREATE INDEX contentitem_conversation_hide_localtime_time_idx ON content_item (conversation_id, hide, local_time, time)");
+            } catch (Error e) {
+                error("Failed to upgrade to database version 17: %s", e.message);
+            }
+        }
     }
 
     public ArrayList<Account> get_accounts() {