[128086] trunk/base/src/cregistry/sql.c

[cal at macports.org]

Revision: 128086
          https://trac.macports.org/changeset/128086
Author:   cal at macports.org
Date:     2014-11-12 15:10:25 -0800 (Wed, 12 Nov 2014)
Log Message:
-----------
base: Drop the low-quality file_binary index from the registry

The file_binary index is a low-quality index according to
https://www.sqlite.org/queryplanner-ng.html#howtofix. Let's remove it in an
attempt to fix our performance problems on Yosemite.

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

Modified: trunk/base/src/cregistry/sql.c
===================================================================
--- trunk/base/src/cregistry/sql.c	2014-11-12 23:01:43 UTC (rev 128085)
+++ trunk/base/src/cregistry/sql.c	2014-11-12 23:10:25 UTC (rev 128086)
@@ -156,7 +156,6 @@
         "CREATE INDEX registry.file_port ON files (id)",
         "CREATE INDEX registry.file_path ON files(path)",
         "CREATE INDEX registry.file_actual ON files(actual_path)",
-        "CREATE INDEX registry.file_binary ON files(binary)",
 
         /* dependency map */
         "CREATE TABLE registry.dependencies (id INTEGER, name TEXT, variants TEXT, "
@@ -360,6 +359,27 @@
             continue;
         }
 
+        if (sql_version(NULL, -1, version, -1, "1.201") < 0) {
+            /* Delete the file_binary index, since it's a low-quality index
+             * according to https://www.sqlite.org/queryplanner-ng.html#howtofix */
+            static char* version_1_201_queries[] = {
+                "DROP INDEX IF EXISTS registry.file_binary",
+                "UPDATE registry.metadata SET value = '1.201' WHERE key = 'version'",
+                "COMMIT",
+                NULL
+            };
+
+            sqlite3_finalize(stmt);
+            stmt = NULL;
+            if (!do_queries(db, version_1_201_queries, errPtr)) {
+                rollback_db(db);
+                return 0;
+            }
+
+            did_update = 1;
+            continue;
+        }
+
         /* add new versions here, but remember to:
          *  - finalize the version query statement and set stmt to NULL
          *  - do _not_ use "BEGIN" in your query list, since a transaction has
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.macosforge.org/pipermail/macports-changes/attachments/20141112/41f2195d/attachment.html>