The latest release of SQLite, the powerful, embeddable public domain SQL database which has found its way into so many applications, is version 3.8.0 which switches over to the project’s “Next Generation Query Planner” (NGQP).
Query planners break down the users SQL queries and work out the best way to get the required results based on the planner’s knowledge of the database tables, indicies and other gathered statistics and a generous helping of the planner’s authors skills in creating effective ways to deduce what to do with that information. Simple selects require little planning but when you move to a world with many joins, working that out becomes quite complex. NGQP for SQLite has been in test for a while and has been taking on those hard test cases, such as the TPC-H Q8 test query which does an eight way join as part of the query and working out better ways to evaluate what the best query plan.
The thing with updating the query planner is that, to the user, there’s no visible changes in syntax or APIs; it all takes place behind the scenes. Other changes do introduce some new syntax. For example, partial indexes adds a WHERE clause to the CREATE INDEX command so that the user can specify what fields they want indexed. They could, for example, only index non-null columns which could, but only could, give better query and write performance (less to search) and smaller database files.
The changelog also notes changes like the query_only_ pragma which stops changes to the database when enabled, the deferforeign_keys pragma which sets when foreign key constraints are enforced (if they are enabled), changes to EXPLAIN QUERY PLAN’s reporting and the ability to get the number of VM instructions a prepared statement is mapped to (for use as a proxy for how much work that prepared statement will be to execute). Source code and more is available to download and as previously mentioned, SQLite is in the public domain.
This article was imported from the original CodeScaling blog