Add distinctions and limitations into SQL since new indexes improve

speed

2 files changed, 17 insertions, 20 deletions

diff --git a/src/db-reader.vala b/src/db-reader.vala
index d34b1689..f9434e1a 100644
--- a/src/db-reader.vala
+++ b/src/db-reader.vala
@@ -171,7 +171,7 @@ public class DbReader : Object
     public uint32[] find_event_ids_for_clause (WhereClause where,
         uint max_events, uint result_type) throws EngineError
     {
-        string sql = "SELECT id FROM event_view ";
+        string sql = "SELECT DISTINCT id FROM event_view ";
         string where_sql = "";
         if (!where.is_empty ())
         {
@@ -297,6 +297,8 @@ public class DbReader : Object
 
         if (where.get_is_simple ())
             sql = sql.replace ("FROM event_view", "FROM event");
+        if (max_events > 0)
+            sql += " LIMIT %u".printf (max_events);
 
         int rc;
         Sqlite.Statement stmt;
@@ -316,13 +318,8 @@ public class DbReader : Object
 
         while ((rc = stmt.step()) == Sqlite.ROW)
         {
-            var event_id = (uint32) uint64.parse(
+            event_ids += (uint32) uint64.parse(
                 stmt.column_text (EventViewRows.ID));
-            // Events are supposed to be contiguous in the database
-            if (event_ids.length == 0 || event_ids[event_ids.length-1] != event_id) {
-                event_ids += event_id;
-                if (event_ids.length == max_events) break;
-            }
         }
         if (rc != Sqlite.DONE && rc != Sqlite.ROW)
         {
diff --git a/src/sql-schema.vala b/src/sql-schema.vala
index 2786a6ae..0bffd2b3 100644
--- a/src/sql-schema.vala
+++ b/src/sql-schema.vala
@@ -555,55 +555,55 @@ namespace Zeitgeist.SQLite
                 """);
             exec_query (database, """
                 CREATE INDEX IF NOT EXISTS event_interpretation
-                    ON event(interpretation, timestamp)
+                    ON event(interpretation, timestamp, id)
                 """);
             exec_query (database, """
                 CREATE INDEX IF NOT EXISTS event_manifestation
-                    ON event(manifestation, timestamp)
+                    ON event(manifestation, timestamp, id)
                 """);
             exec_query (database, """
                 CREATE INDEX IF NOT EXISTS event_actor
-                    ON event(actor, timestamp)
+                    ON event(actor, timestamp, id)
                 """);
             exec_query (database, """
                 CREATE INDEX IF NOT EXISTS event_origin
-                    ON event(origin, timestamp)
+                    ON event(origin, timestamp, id)
                 """);
             exec_query (database, """
                 CREATE INDEX IF NOT EXISTS event_subj_id
-                    ON event(subj_id, timestamp, subj_interpretation)
+                    ON event(subj_id, timestamp, subj_interpretation, id)
                 """);
             exec_query (database, """
                 CREATE INDEX IF NOT EXISTS event_subj_id_current
-                    ON event(subj_id_current, timestamp, subj_interpretation)
+                    ON event(subj_id_current, timestamp, subj_interpretation, id)
                 """);
             exec_query (database, """
                 CREATE INDEX IF NOT EXISTS event_subj_interpretation
-                    ON event(subj_interpretation, timestamp, subj_id)
+                    ON event(subj_interpretation, timestamp, subj_id, id)
                 """);
             exec_query (database, """
                 CREATE INDEX IF NOT EXISTS event_subj_manifestation
-                    ON event(subj_manifestation, timestamp, subj_id)
+                    ON event(subj_manifestation, timestamp, subj_id, id)
                 """);
             exec_query (database, """
                 CREATE INDEX IF NOT EXISTS event_subj_origin
-                    ON event(subj_origin, timestamp, subj_interpretation, subj_id)
+                    ON event(subj_origin, timestamp, subj_interpretation, subj_id, id)
                 """);
             exec_query (database, """
                 CREATE INDEX IF NOT EXISTS event_subj_origin_current
-                    ON event(subj_origin_current, timestamp, subj_interpretation, subj_id)
+                    ON event(subj_origin_current, timestamp, subj_interpretation, subj_id, id)
                 """);
             exec_query (database, """
                 CREATE INDEX IF NOT EXISTS event_subj_mimetype
-                    ON event(subj_mimetype, timestamp)
+                    ON event(subj_mimetype, timestamp, id)
                 """);
             exec_query (database, """
                 CREATE INDEX IF NOT EXISTS event_subj_text
-                    ON event(subj_text, timestamp)
+                    ON event(subj_text, timestamp, id)
                 """);
             exec_query (database, """
                 CREATE INDEX IF NOT EXISTS event_subj_storage
-                    ON event(subj_storage, timestamp)
+                    ON event(subj_storage, timestamp, id)
                 """);
         }