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