[epiphany] gsb-storage: Use indexes to speed up SELECT queries

[Gabriel Ivașcu <gabrielivascu src gnome org>]

commit 203110a37280e83a19e1949b85228e3f92560660
Author: Gabriel Ivascu <gabrielivascu gnome org>
Date:   Thu Oct 19 21:30:51 2017 +0300

    gsb-storage: Use indexes to speed up SELECT queries
    
    In 99% of cases (i.e. safe websites which can be resolved based on the
    local database of threats without querying the server), this improves
    the URL verification time by a factor of ~150x, bringing a considerable
    speed-up to the page loading time. On the other hand, it increases the
    initial database update time by ~48% and the database file size by ~14%.
    However, this is a reasonable trade-off because we care more about the
    overall performance of URL verification since that affects directly the
    page loading time.

 lib/safe-browsing/ephy-gsb-storage.c |   18 +++++++++++++++++-
 1 files changed, 17 insertions(+), 1 deletions(-)
---
diff --git a/lib/safe-browsing/ephy-gsb-storage.c b/lib/safe-browsing/ephy-gsb-storage.c
index 157ff4a..aec72db 100644
--- a/lib/safe-browsing/ephy-gsb-storage.c
+++ b/lib/safe-browsing/ephy-gsb-storage.c
@@ -35,7 +35,7 @@
 #define BATCH_SIZE 199
 
 /* Increment schema version if you modify the database table structure. */
-#define SCHEMA_VERSION 1
+#define SCHEMA_VERSION 2
 
 struct _EphyGSBStorage {
   GObject parent_instance;
@@ -252,6 +252,14 @@ ephy_gsb_storage_init_hash_prefix_table (EphyGSBStorage *self)
     return FALSE;
   }
 
+  sql = "CREATE INDEX idx_hash_prefix_cue ON hash_prefix (cue)";
+  ephy_sqlite_connection_execute (self->db, sql, &error);
+  if (error) {
+    g_warning ("Failed to create idx_hash_prefix_cue index: %s", error->message);
+    g_error_free (error);
+    return FALSE;
+  }
+
   return TRUE;
 }
 
@@ -282,6 +290,14 @@ ephy_gsb_storage_init_hash_full_table (EphyGSBStorage *self)
     return FALSE;
   }
 
+  sql = "CREATE INDEX idx_hash_full_value ON hash_full (value)";
+  ephy_sqlite_connection_execute (self->db, sql, &error);
+  if (error) {
+    g_warning ("Failed to create idx_hash_full_value index: %s", error->message);
+    g_error_free (error);
+    return FALSE;
+  }
+
   return TRUE;
 }