SQLite

Check-in [da9a2e5aa9]
Login

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

Overview
Comment:Fix formatting errors in the previous commit.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA3-256: da9a2e5aa977f7e8e9e4365f7b34bb4f482029a3d44646100773cedc8ea9b959
User & Date: dan 2017-04-21 19:56:53.579
Context
2017-04-21
19:58
Another minor formatting fix. (check-in: 9fa2ce3c2b user: dan tags: schemalint)
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)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/expert/README.md. 
1
2
3
4
5
6
7
8
9
10
11
12
13

14
15
16
17
18
19
20

## 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.








|

|

|
|
>








1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

## 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.
     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 candidate indexes
     - indexes that the planner might have made use of in the previous step,
     had they been available.

  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.
︙︙
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82


Or an entire text file worth of queries with:

<pre>
  ./sqlite3_expert -file &lt;text-file&gt; 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 &lt;sql-query&gt; test.db

  # Do not generate any statistics at all:
  ./sqlite3_expert -sample 0 -sql &lt;sql-query&gt; test.db
</pre>








|

|











64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83


Or an entire text file worth of queries with:

<pre>
  ./sqlite3_expert -file &lt;text-file&gt; 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 &lt;sql-query&gt; test.db

  # Do not generate any statistics at all:
  ./sqlite3_expert -sample 0 -sql &lt;sql-query&gt; test.db
</pre>