1## SQLite Expert Extension 2 3This folder contains code for a simple system to propose useful indexes 4given 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 25The SQLite expert C API is defined in sqlite3expert.h. Most uses will proceed 26as 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 47Refer to comments in sqlite3expert.h for further details. 48 49# sqlite3_expert application 50 51The file "expert.c" contains the code for a command line application that 52uses 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 58Assuming the database is named "test.db", it can then be run to analyze a 59single query: 60 61<pre> 62 ./sqlite3_expert -sql <sql-query> test.db 63</pre> 64 65Or an entire text file worth of queries with: 66 67<pre> 68 ./sqlite3_expert -file <text-file> test.db 69</pre> 70 71By default, sqlite3\_expert generates index statistics using all the data in 72the user database. For a large database, this may be prohibitively time 73consuming. The "-sample" option may be used to configure sqlite3\_expert to 74generate statistics based on an integer percentage of the user database as 75follows: 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> 84