| 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 <sql-query> test.db |
| 63 |
</pre> |
| 64 |
|
| 65 |
Or an entire text file worth of queries with: |
| 66 |
|
| 67 |
<pre> |
| 68 |
./sqlite3_expert -file <text-file> 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 <sql-query> test.db |
| 80 |
|
| 81 |
# Do not generate any statistics at all: |
| 82 |
./sqlite3_expert -sample 0 -sql <sql-query> test.db |
| 83 |
</pre> |