Index: pages/cli.in ================================================================== --- pages/cli.in +++ pages/cli.in @@ -858,10 +858,80 @@
This command works the same way as the --create command, except that
it does not delete the current archive before commencing. New versions of
files silently replace existing files with the same names, but otherwise
the initial contents of the archive (if any) remain intact.
+
For most non-trivial SQL databases, the key to performance is creating +the right SQL indexes. In this context "the right SQL indexes" means those +that cause the queries that an application needs to optimize run fast. The +".expert" command can assist with this by proposing indexes that might +assist with specific queries, were they present in the database. + +
The ".expert" command is issued first, followed by the SQL query
+on a separate line. For example, consider the following session:
+
+
In the above, the user creates the database schema (a single table - "x1"), +and then uses the ".expert" command to analyze a query, in this case +"SELECT * FROM x1 WHERE a=? AND b>?". The shell tool recommends that the +user create a new index (index "x1_idx_000123a7") and outputs the plan +that the query would use in [EXPLAIN QUERY PLAN] format. The user then creates +an index with an equivalent schema and runs the analysis on the same query +again. This time the shell tool does not recommend any new indexes, and +outputs the plan that SQLite will use for the query given the existing +indexes. + +
The ".expert" command accepts the following options: + +
Option | Purpose + |
---|---|
--verbose + | If present, output a more verbose report for each query analyzed. + |
--sample PERCENT + | By default, the ".expert" command recommends indexes based on the
+ query and database schema alone. This is similar to the way the
+ [SQLite query planner] selects indexes for queries if the user has not
+ run the [ANALYZE] command on the database to generate data
+ distribution statistics.
+
+ If this option is pass a non-zero argument, the ".expert" command
+ generates similar data distribution statistics for all indexes
+ considered based on PERCENT percent of the rows currently stored in
+ each database table. For databases with unusual data distributions,
+ this may lead to better index recommendations, particularly if the
+ application intends to run ANALYZE.
+
+ For small databases and modern CPUs, there is usually no reason not
+ to pass "--sample 100". However, gathering data distribution
+ statistics can be expensive for large database tables. If the
+ operation is too slow, try passing a smaller value for the --sample
+ option.
+ |
Th functionality described in this section may be integrated into other
+applications or tools using the
+
+SQLite expert extension code.
+
There are many other dot-commands available in the command-line shell. See the ".help" command for a complete list for any particular