Changeset 128274 for trunk/base


Ignore:
Timestamp:
Nov 17, 2014, 10:34:04 PM (5 years ago)
Author:
cal@…
Message:

base: cregistry: Improve query performance in file.c (all those queries would use the wrong index on Yosemite)

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/base/src/cregistry/file.c

    r117632 r128274  
    106106    sqlite3_stmt* stmt = NULL;
    107107    reg_file* file = NULL;
    108     char* query = "SELECT id, path FROM registry.files WHERE id=? AND path=?";
     108    char* query = "SELECT id, path FROM registry.files "
     109#if SQLITE_VERSION_NUMBER >= 3006004
     110        /* if the version of SQLite supports it force the usage of the index on
     111         * path, rather than the one on id which has a lot less discriminative
     112         * power and leads to very slow queries. This is needed for the new
     113         * query planner introduced in 3.8.0 which would not use the correct
     114         * index automatically. */
     115        "INDEXED BY file_path "
     116#endif
     117        "WHERE id=? AND path=?";
    109118    int lower_bound = 0;
    110119
     
    236245    char* query;
    237246    const char *text;
    238     query = sqlite3_mprintf("SELECT %q FROM registry.files WHERE id=%lld "
    239             "AND path='%q'", key, file->key.id, file->key.path);
     247    query = sqlite3_mprintf(
     248            "SELECT %q FROM registry.files "
     249#if SQLITE_VERSION_NUMBER >= 3006004
     250            /* if the version of SQLite supports it force the usage of the index
     251             * on path, rather than the one on id which has a lot less
     252             * discriminative power and leads to very slow queries. This is
     253             * needed for the new query planner introduced in 3.8.0 which would
     254             * not use the correct index automatically. */
     255            "INDEXED BY file_path "
     256#endif
     257            "WHERE id=%lld AND path='%q'", key, file->key.id, file->key.path);
    240258    if (sqlite3_prepare_v2(reg->db, query, -1, &stmt, NULL) == SQLITE_OK) {
    241259        int r;
     
    291309    sqlite3_stmt* stmt = NULL;
    292310    char* query;
    293     query = sqlite3_mprintf("UPDATE registry.files SET %q = '%q' WHERE id=%lld "
    294             "AND path='%q'", key, value, file->key.id, file->key.path);
     311    query = sqlite3_mprintf(
     312            "UPDATE registry.files "
     313#if SQLITE_VERSION_NUMBER >= 3006004
     314            /* if the version of SQLite supports it force the usage of the index
     315             * on path, rather than the one on id which has a lot less
     316             * discriminative power and leads to very slow queries. This is
     317             * needed for the new query planner introduced in 3.8.0 which would
     318             * not use the correct index automatically. */
     319            "INDEXED BY file_path "
     320#endif
     321            "SET %q = '%q' WHERE id=%lld AND path='%q'", key, value, file->key.id, file->key.path);
    295322    if (sqlite3_prepare_v2(reg->db, query, -1, &stmt, NULL) == SQLITE_OK) {
    296323        int r;
Note: See TracChangeset for help on using the changeset viewer.