ext/expert/README.md

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