remove redundant indexes

2 files changed, 30 insertions, 6 deletions

diff --git a/src/sql-schema.vala b/src/sql-schema.vala
index 0bffd2b3..17e6ff7a 100644
--- a/src/sql-schema.vala
+++ b/src/sql-schema.vala
@@ -36,7 +36,7 @@ namespace Zeitgeist.SQLite
     {
 
         public const string CORE_SCHEMA = "core";
-        public const int CORE_SCHEMA_VERSION = 8;
+        public const int CORE_SCHEMA_VERSION = 9;
 
         private const string DATABASE_CREATION = "database_creation";
 
@@ -51,7 +51,6 @@ namespace Zeitgeist.SQLite
                 // most likely a new DB
                 setup_database (database);
                 create_schema (database);
-                create_basic_indices (database);
                 create_event_indices (database);
 
                 // set database creation date
@@ -60,7 +59,7 @@ namespace Zeitgeist.SQLite
                     Timestamp.from_now ());
                 exec_query (database, schema_sql);
             }
-            else if (schema_version >= 3 && schema_version <= 7)
+            else if (schema_version >= 3 && schema_version <= 8)
             {
                 backup_database ();
 
@@ -112,8 +111,7 @@ namespace Zeitgeist.SQLite
                 // Create any missing tables and indices
                 create_schema (database);
                 drop_event_indices (database);
-                create_basic_indices (database);
-                create_event_indices (database);
+                drop_basic_indices (database);
 
                 // Migrate data to the new tables and delete the old ones
                 foreach (unowned string table in tables)
@@ -172,7 +170,8 @@ namespace Zeitgeist.SQLite
                     """);
 
                 exec_query (database, "COMMIT");
-
+                create_event_indices (database);
+                exec_query (database, "ANALYZE");
                 message ("Upgraded database to schema version %d.",
                     CORE_SCHEMA_VERSION);
             }
@@ -607,6 +606,22 @@ namespace Zeitgeist.SQLite
                 """);
         }
 
+        /*
+         * Drop indices for all auxiliary tables.
+         */
+        public static void drop_basic_indices (Sqlite.Database database)
+            throws EngineError
+        {
+            exec_query (database, "DROP INDEX IF EXISTS uri_value");
+            exec_query (database, "DROP INDEX IF EXISTS interpretation_value");
+            exec_query (database, "DROP INDEX IF EXISTS manifestation_value");
+            exec_query (database, "DROP INDEX IF EXISTS mimetype_value");
+            exec_query (database, "DROP INDEX IF EXISTS actor_value");
+            exec_query (database, "DROP INDEX IF EXISTS text_value");
+            exec_query (database, "DROP INDEX IF EXISTS storage_value");
+            exec_query (database, "DROP INDEX IF EXISTS extensions_conf_key");
+        }
+
         public static void drop_event_indices (Sqlite.Database database)
             throws EngineError
         {
diff --git a/tools/development/README b/tools/development/README
index 091abbd2..4241305f 100644
--- a/tools/development/README
+++ b/tools/development/README
@@ -25,3 +25,12 @@
 #       --queries tools/development/query_sets/timerange_always.txt
 #  tools/development/query_timings.py --plot trunk.json --type overall \
 #       -o benchmark.svg
+
+# A good test for trunk and a release is as follows:
+
+tools/development/query_timings.py --name "trunk" -o synapse_trunk.json --queries tools/development/query_sets/synapse.txt && tools/development/query_timings.py --name "trunk" -o synapse_unlimited_trunk.json --queries tools/development/query_sets/synapse-unlimited.txt && tools/development/query_timings.py --name "trunk" -o timerange_always_trunk.json --queries tools/development/query_sets/timerange_always.txt && tools/development/query_timings.py --name "trunk" -o timerange_interval_trunk.json --queries tools/development/query_sets/timerange_interval.txt && tools/development/query_timings.py --name "trunk" -o jumplist_trunk.json --queries tools/development/query_sets/jumplist.txt
+
+tools/development/query_timings.py --name "0.9.5" -o jumplist_0.9.5.json --queries tools/development/query_sets/jumplist.txt && 
+tools/development/query_timings.py --name "0.9.5" -o synapse_0.9.5.json --queries tools/development/query_sets/synapse.txt && tools/development/query_timings.py --name "0.9.5" -o synapse_unlimited_0.9.5.json --queries tools/development/query_sets/synapse-unlimited.txt && tools/development/query_timings.py --name "0.9.5" -o timerange_always_0.9.5.json --queries tools/development/query_sets/timerange_always.txt && tools/development/query_timings.py --name "0.9.5" -o timerange_interval_0.9.5.json --queries tools/development/query_sets/timerange_interval.txt
+
+tools/development/query_timings.py --plot jumplist_0.9.5.json --plot jumplist_trunk.json --type overall -o jumplist.svg && tools/development/query_timings.py --plot synapse_0.9.5.json  --plot synapse_trunk.json --type overall -o synapse.svg && tools/development/query_timings.py --plot synapse_unlimited_0.9.5.json --plot synapse_unlimited_trunk.json --type overall -o synapse_unlimited.svg && tools/development/query_timings.py --plot timerange_always_0.9.5.json --plot timerange_always_trunk.json --type overall -o timerange_always.svg && tools/development/query_timings.py --plot timerange_interval_0.9.5.json --plot timerange_interval_trunk.json --type overall -o timerange_interval.svg