/ Check-in [3b2ff4e0]
Login

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

Overview
Comment:Update the README.md file in the ext/expert/ directory.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA3-256: 3b2ff4e0692dfca395d4523b7d5cd0dfd5c319c1072a2a873631fa477cee0b79
User & Date: dan 2017-04-21 19:53:39
Context
2017-04-21
19:56
Fix formatting errors in the previous commit. check-in: da9a2e5a user: dan tags: schemalint
19:53
Update the README.md file in the ext/expert/ directory. check-in: 3b2ff4e0 user: dan tags: schemalint
2017-04-20
17:35
Merge latest trunk changes into this branch. check-in: b1533bc4 user: dan tags: schemalint
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/expert/README.md.

     1      1   ## SQLite Expert Extension
     2      2   
     3      3   This folder contains code for a simple system to propose useful indexes
     4      4   given a database and a set of SQL queries. It works as follows:
     5      5   
     6      6     1. The user database schema is copied to a temporary database.
     7      7   
     8         -  1. All SQL queries are prepared against the temporary database. The
     9         -     **sqlite3\_whereinfo\_hook()** API is used to record information regarding
    10         -     the WHERE and ORDER BY clauses attached to each query.
            8  +  1. All SQL queries are prepared against the temporary database. I
            9  +     Information regarding the WHERE and ORDER BY clauses, and other query
           10  +     features that affect index selection, are recorded.
    11     11   
    12     12     1. The information gathered in step 2 is used to create (possibly a large
    13     13        number of) candidate indexes.
           14  +
           15  +  1. A subset of the data in the user database is used to generate statistics
           16  +     for all existing indexes and the candidate indexes generated in step 3
           17  +     above.
    14     18   
    15     19     1. The SQL queries are prepared a second time. If the planner uses any
    16     20        of the indexes created in step 3, they are recommended to the user.
    17     21   
    18         -No ANALYZE data is available to the planner in step 4 above. This can lead to sub-optimal results.
    19         -
    20         -This extension requires that SQLite be built with the 
    21         -SQLITE\_ENABLE\_WHEREINFO\_HOOK pre-processor symbol defined.
    22         -
    23     22   # C API
    24     23   
    25     24   The SQLite expert C API is defined in sqlite3expert.h. Most uses will proceed
    26     25   as follows:
    27     26   
    28     27     1. An sqlite3expert object is created by calling **sqlite3\_expert\_new()**.
    29     28        A database handle opened by the user is passed as an argument.
    30     29   
    31     30     1. The sqlite3expert object is configured with one or more SQL statements
    32     31        by making one or more calls to **sqlite3\_expert\_sql()**. Each call may
    33     32        specify a single SQL statement, or multiple statements separated by
    34     33        semi-colons.
           34  +  
           35  +  1. Optionally, the **sqlite3\_expert\_config()** API may be used to 
           36  +     configure the size of the data subset used to generate index statistics.
           37  +     Using a smaller subset of the data can speed up the analysis.
    35     38   
    36     39     1. **sqlite3\_expert\_analyze()** is called to run the analysis.
    37     40   
    38     41     1. One or more calls are made to **sqlite3\_expert\_report()** to extract
    39     42        components of the results of the analysis.
    40     43   
    41     44     1. **sqlite3\_expert\_destroy()** is called to free all resources.
................................................................................
    44     47   
    45     48   # sqlite3_expert application
    46     49   
    47     50   The file "expert.c" contains the code for a command line application that
    48     51   uses the API described above. It can be compiled with (for example):
    49     52   
    50     53   <pre>
    51         -  gcc -O2 -DSQLITE_ENABLE_WHEREINFO_HOOK sqlite3.c expert.c sqlite3expert.c -o sqlite3_expert
           54  +  gcc -O2 sqlite3.c expert.c sqlite3expert.c -o sqlite3_expert
    52     55   </pre>
    53     56   
    54         -Assuming the database is "test.db", it can then be run to analyze a single query:
           57  +Assuming the database is named "test.db", it can then be run to analyze a
           58  +single query:
    55     59   
    56     60   <pre>
    57     61     ./sqlite3_expert -sql &lt;sql-query&gt; test.db
    58     62   </pre>
    59     63   
    60     64   Or an entire text file worth of queries with:
    61     65   
    62     66   <pre>
    63     67     ./sqlite3_expert -file &lt;text-file&gt; test.db
    64     68   </pre>
    65     69   
           70  +By default, sqlite3_expert generates index statistics using all the data in
           71  +the user database. For a large database, this may be prohibitively time
           72  +consuming. The "-sample" option may be used to configure sqlite3_expert to
           73  +generate statistics based on an integer percentage of the user database as
           74  +follows:
    66     75   
           76  +<pre>
           77  +  # Generate statistics based on 25% of the user database rows:
           78  +  ./sqlite3_expert -sample 25 -sql &lt;sql-query&gt; test.db
    67     79   
    68         -
           80  +  # Do not generate any statistics at all:
           81  +  ./sqlite3_expert -sample 0 -sql &lt;sql-query&gt; test.db
           82  +</pre>