The SQLite Expert extension

The SQLite Expert extension is a very useful extension that aims to suggest indexes from a series of queries. It is in active development by the SQLite team and its source code is available here.

Starting from an already opened SQLite database and a query, an index can be automatically generated from the following code (omitting error checking for brevity):

void suggest_index (sqlite3 *db, const char *sql, int percentage) {
    // setup a new sqlite3expert
    sqlite3expert *p = sqlite3_expert_new(db, NULL);

    // set the SQL to be analyzed
    int rc = sqlite3_expert_sql(p, sql, NULL);

    // by default, sqlite3expert generates index statistics using
    // all the data in the user database. For a large database,
    // this may be prohibitively time consuming, so you can choose
    // to generate statistics based on an integer percentage of the 
    // user database
    if (percentage > 0 && percentage < 100) {
        rc = sqlite3_expert_config(p, EXPERT_CONFIG_SAMPLE, percentage);
    }

    // start real query analysis
    rc = sqlite3_expert_analyze(p, NULL);

    // count the number of generated responses
    int count = sqlite3_expert_count(p);

    // loop through each response and retrieve index(es) only
    for (int i=0; i<count; ++i) {
        const char *idx = sqlite3_expert_report(p, i, EXPERT_REPORT_INDEXES);

        // print suggested index
        printf("Index %d: %s\n", i, idx);
    }

    // final cleanup
    sqlite3_expert_destroy(p);
}

You can compile the expert extension as a standalone executable if you don't want to write C code:

gcc -O2 sqlite3.c expert.c sqlite3expert.c -o sqlite3_expert

sqlite3_expert can be used as a convenient command line interface:

# Generate statistics based on 25% of the user database rows:
./sqlite3_expert -sample 25 -sql <sql-query> test.db

In SQLite Cloud Dashboard the Analyzer section is built on top of the SQLite Expert extension. The Analyzer panel is a powerful tool that collects and categorizes all the queries executed on your cluster based on their execution time. It allows for intelligent and proactive analysis and provides recommendations on which indexes to use to optimize frequently used queries.

Once an index has been computed you can also choose to automatically apply it to your database and that change will be propagated to all the nodes of your cluster:

Overall, the SQLite Expert extension is a powerful tool that can help users to optimize and manage their SQLite databases more effectively. With its support for index analysis and query optimization, the extension provides a comprehensive set of features that can help users to improve database performance and streamline their database management workflows.