[128274] trunk/base/src/cregistry/file.c

[cal at macports.org]

Revision: 128274
          https://trac.macports.org/changeset/128274
Author:   cal at macports.org
Date:     2014-11-17 14:34:04 -0800 (Mon, 17 Nov 2014)
Log Message:
-----------
base: cregistry: Improve query performance in file.c (all those queries would use the wrong index on Yosemite)

Modified Paths:
--------------
    trunk/base/src/cregistry/file.c

Modified: trunk/base/src/cregistry/file.c
===================================================================
--- trunk/base/src/cregistry/file.c	2014-11-17 21:08:41 UTC (rev 128273)
+++ trunk/base/src/cregistry/file.c	2014-11-17 22:34:04 UTC (rev 128274)
@@ -105,7 +105,16 @@
         reg_error* errPtr) {
     sqlite3_stmt* stmt = NULL;
     reg_file* file = NULL;
-    char* query = "SELECT id, path FROM registry.files WHERE id=? AND path=?";
+    char* query = "SELECT id, path FROM registry.files "
+#if SQLITE_VERSION_NUMBER >= 3006004
+        /* if the version of SQLite supports it force the usage of the index on
+         * path, rather than the one on id which has a lot less discriminative
+         * power and leads to very slow queries. This is needed for the new
+         * query planner introduced in 3.8.0 which would not use the correct
+         * index automatically. */
+        "INDEXED BY file_path "
+#endif
+        "WHERE id=? AND path=?";
     int lower_bound = 0;
 
     if ((sqlite3_prepare_v2(reg->db, query, -1, &stmt, NULL) == SQLITE_OK)
@@ -235,8 +244,17 @@
     sqlite3_stmt* stmt = NULL;
     char* query;
     const char *text;
-    query = sqlite3_mprintf("SELECT %q FROM registry.files WHERE id=%lld "
-            "AND path='%q'", key, file->key.id, file->key.path);
+    query = sqlite3_mprintf(
+            "SELECT %q FROM registry.files "
+#if SQLITE_VERSION_NUMBER >= 3006004
+            /* if the version of SQLite supports it force the usage of the index
+             * on path, rather than the one on id which has a lot less
+             * discriminative power and leads to very slow queries. This is
+             * needed for the new query planner introduced in 3.8.0 which would
+             * not use the correct index automatically. */
+            "INDEXED BY file_path "
+#endif
+            "WHERE id=%lld AND path='%q'", key, file->key.id, file->key.path);
     if (sqlite3_prepare_v2(reg->db, query, -1, &stmt, NULL) == SQLITE_OK) {
         int r;
         do {
@@ -290,8 +308,17 @@
     int result = 0;
     sqlite3_stmt* stmt = NULL;
     char* query;
-    query = sqlite3_mprintf("UPDATE registry.files SET %q = '%q' WHERE id=%lld "
-            "AND path='%q'", key, value, file->key.id, file->key.path);
+    query = sqlite3_mprintf(
+            "UPDATE registry.files "
+#if SQLITE_VERSION_NUMBER >= 3006004
+            /* if the version of SQLite supports it force the usage of the index
+             * on path, rather than the one on id which has a lot less
+             * discriminative power and leads to very slow queries. This is
+             * needed for the new query planner introduced in 3.8.0 which would
+             * not use the correct index automatically. */
+            "INDEXED BY file_path "
+#endif
+            "SET %q = '%q' WHERE id=%lld AND path='%q'", key, value, file->key.id, file->key.path);
     if (sqlite3_prepare_v2(reg->db, query, -1, &stmt, NULL) == SQLITE_OK) {
         int r;
         do {
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.macosforge.org/pipermail/macports-changes/attachments/20141117/db11e446/attachment.html>