Documentation Source Text

Check-in [7f57c342de]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add docs for experimental ".expert" command.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:7f57c342dec8b0ad62b24895c734c0faaa4f6700257363a6e2676af7a1d0238a
User & Date: dan 2017-12-21 18:50:20
Context
2018-01-01
14:36
Update to the speed-size-graph spreadsheet. check-in: 727ad4e477 user: drh tags: trunk
2017-12-21
18:50
Add docs for experimental ".expert" command. check-in: 7f57c342de user: dan tags: trunk
2017-12-19
17:34
Add docs for a proposed addition to the SQLite shell tool. Closed-Leaf check-in: cd6f445782 user: dan tags: expert-in-shell
2017-12-16
17:20
Add documentation for the "swarmvtab" extension. check-in: 9cc758b326 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/cli.in.

856
857
858
859
860
861
862









































































863
864
865
866
867
868
869
<h2> SQLAR Update Command </h2>

<p> 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.










































































<tcl>hd_fragment dotother</tcl>
<h1>Other Dot Commands</h1>

<p>There are many other dot-commands available in the command-line
shell.  See the ".help" command for a complete list for any particular
version and build of SQLite.








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
<h2> SQLAR Update Command </h2>

<p> 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.

<tcl>hd_fragment expert</tcl>
<h1>Index Recommendations (SQLite Expert)</h1>

<p><b>Note: This command is experimental. It may be removed or the 
interface modified in incompatible ways at some point in the future.

<p>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.

<p>The ".expert" command is issued first, followed by the SQL query 
on a separate line. For example, consider the following session:

<codeblock>
sqlite&gt; CREATE TABLE x1(a, b, c);                  <i>-- Create table in database </i>
sqlite&gt; .expert
sqlite&gt; SELECT * FROM x1 WHERE a=? AND b&gt;?;        <i>-- Analyze this SELECT </i>
CREATE INDEX x1_idx_000123a7 ON x1(a, b);

0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (a=? AND b&gt;?)

sqlite&gt; CREATE INDEX x1ab ON x1(a, b);             <i>-- Create the recommeded index </i>
sqlite&gt; .expert
sqlite&gt; SELECT * FROM x1 WHERE a=? AND b&gt;?;        <i>-- Re-analyze the same SELECT </i>
(no new indexes)

0|0|0|SEARCH TABLE x1 USING INDEX x1ab (a=? AND b&gt;?)
</codeblock>

<p>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&gt;?". 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.

<p>The ".expert" command accepts the following options:

<table striped=1>
<tr><th> Option <th> Purpose
<tr><td> --verbose 
    <td> If present, output a more verbose report for each query analyzed.
<tr><td> --sample&nbsp;PERCENT 
    <td> 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. 
         <div style="margin-top:1ex">
         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.
         <div style="margin-top:1ex">
         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.
</table>

<p>Th functionality described in this section may be integrated into other
applications or tools using the 
<a href="http://www.sqlite.org/src/dir?ci=trunk&name=ext/expert">
SQLite expert extension</a> code.

<tcl>hd_fragment dotother</tcl>
<h1>Other Dot Commands</h1>

<p>There are many other dot-commands available in the command-line
shell.  See the ".help" command for a complete list for any particular
version and build of SQLite.