[128113] trunk/base/src/cregistry/entry.c

[cal at macports.org]

Revision: 128113
          https://trac.macports.org/changeset/128113
Author:   cal at macports.org
Date:     2014-11-13 13:03:30 -0800 (Thu, 13 Nov 2014)
Log Message:
-----------
base: cregistry/entry.c: force use of a specific high-quality index to fix slowness with SQLite's new query planner first shipped with Yosemite, closes #45645

See https://lists.macosforge.org/pipermail/macports-dev/2014-November/028747.html.

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

Modified: trunk/base/src/cregistry/entry.c
===================================================================
--- trunk/base/src/cregistry/entry.c	2014-11-13 20:23:22 UTC (rev 128112)
+++ trunk/base/src/cregistry/entry.c	2014-11-13 21:03:30 UTC (rev 128113)
@@ -1027,8 +1027,16 @@
     sqlite3_stmt* update = NULL;
     char* select_query = "SELECT id FROM registry.files WHERE actual_path=? "
         "AND active";
-    char* update_query = "UPDATE registry.files SET actual_path=?, active=1 "
-        "WHERE path=? AND id=?";
+    char* update_query = "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 actual_path=?, active=1 WHERE path=? AND id=?";
 
     /* if as_files wasn't specified, activate as the original files */
     if (as_files == NULL) {
@@ -1126,7 +1134,16 @@
     int result = 1;
     int i;
     sqlite3_stmt* stmt = NULL;
-    char* query = "UPDATE registry.files SET active=0 WHERE actual_path=? AND id=?";
+    char* query = "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_actual "
+#endif
+        "SET active=0 WHERE actual_path=? AND id=?";
     if ((sqlite3_prepare_v2(reg->db, query, -1, &stmt, NULL) == SQLITE_OK)
             && (sqlite3_bind_int64(stmt, 2, entry->id) == SQLITE_OK)) {
         for (i=0; i<file_count && result; i++) {
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.macosforge.org/pipermail/macports-changes/attachments/20141113/f7759808/attachment.html>