[geary/wip/725929-db-opt: 2/3] Rejigger indices to make slow queries fast

[Charles Lindsay <clindsay src gnome org>]

commit 4a7c6a5572c3160a4758a98df3a1ad6d9756d4c3
Author: Charles Lindsay <chaz yorba org>
Date:   Tue Mar 11 11:48:10 2014 -0700

    Rejigger indices to make slow queries fast

 sql/CMakeLists.txt  |    1 +
 sql/version-021.sql |   13 +++++++++++++
 2 files changed, 14 insertions(+), 0 deletions(-)
---
diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt
index be17579..c63ed8f 100644
--- a/sql/CMakeLists.txt
+++ b/sql/CMakeLists.txt
@@ -20,3 +20,4 @@ install(FILES version-017.sql DESTINATION ${SQL_DEST})
 install(FILES version-018.sql DESTINATION ${SQL_DEST})
 install(FILES version-019.sql DESTINATION ${SQL_DEST})
 install(FILES version-020.sql DESTINATION ${SQL_DEST})
+install(FILES version-021.sql DESTINATION ${SQL_DEST})
diff --git a/sql/version-021.sql b/sql/version-021.sql
new file mode 100644
index 0000000..1f70bf0
--- /dev/null
+++ b/sql/version-021.sql
@@ -0,0 +1,13 @@
+--
+-- Some queries that hit the MessageLocationTable, like those used by the email
+-- prefetcher, were slow because we didn't have a covering index.  This makes
+-- an index that *is* covering, for the cases in question anyway.  Since we
+-- (should) never care about ordering without folder_id, and since folder_id
+-- comes first here so this index effectively indexes queries on just that
+-- field too, we can also drop the old, ineffective indices.
+--
+
+DROP INDEX MessageLocationTableFolderIdIndex;
+DROP INDEX MessageLocationTableOrderingIndex;
+CREATE INDEX MessageLocationTableFolderIDOrderingIndex
+    ON MessageLocationTable(folder_id, ordering);