Documentation Source Text

Check-in [906016ee92]
Login

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

Overview
Comment:Add initial automatic indexing documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 906016ee923faf6a96dd8b6f0e205368e761dbae
User & Date: drh 2010-04-12 13:57:01
Context
2010-05-06
23:48
Minor edits on the way toward 3.7.0 documentation. The wal.html document is created but is still mostly just a stub. check-in: 019b60379f user: drh tags: trunk
2010-04-12
13:57
Add initial automatic indexing documentation. check-in: 906016ee92 user: drh tags: trunk
2010-03-29
19:57
Updates prior to the release of 3.6.23.1 check-in: 8327c0b926 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/compile.in.

   537    537     [AUTOINCREMENT] functionality. When this 
   538    538     is macro is defined, columns declared as 
   539    539     "[INTEGER PRIMARY KEY] AUTOINCREMENT"
   540    540     behave in the same way as columns declared as "[INTEGER PRIMARY KEY]" when a 
   541    541     NULL is inserted. The sqlite_sequence system table is neither created, nor
   542    542     respected if it already exists.
   543    543   }
          544  +
          545  +COMPILE_OPTION {SQLITE_OMIT_AUTOMATIC_INDEX} {
          546  +  This option is used to omit the 
          547  +  [automatic indexing] functionality.
          548  +}
   544    549   
   545    550   COMPILE_OPTION {SQLITE_OMIT_AUTOINIT} {
   546    551     For backwards compatibility with older versions of SQLite that lack
   547    552     the [sqlite3_initialize()] interface, the [sqlite3_initialize()] interface
   548    553     is called automatically upon entry to certain key interfaces such as
   549    554     [sqlite3_open()], [sqlite3_vfs_register()], and [sqlite3_mprintf()].
   550    555     The overhead of invoking [sqlite3_initialize()] automatically in this

Changes to pages/optoverview.in.

    17     17   proc PARAGRAPH {text} {
    18     18     # regsub -all "/(\[a-zA-Z0-9\]+)/" $text {<i>\1</i>} t2
    19     19     regsub -all "\\*(\[^\n*\]+)\\*" $text {<tt><b><big>\1</big></b></tt>} t3
    20     20     hd_resolve "<p>$t3</p>\n"
    21     21   }
    22     22   set level(0) 0
    23     23   set level(1) 0
    24         -proc HEADING {n name {tag {}}} {
    25         -  hd_fragment $tag
           24  +proc HEADING {n name args} {
           25  +  if {[llength $args]>0} {
           26  +    eval hd_fragment $args
           27  +  }
    26     28     global level
    27     29     incr level($n)
    28     30     for {set i [expr {$n+1}]} {$i<10} {incr i} {
    29     31       set level($i) 0
    30     32     }
    31     33     if {$n==0} {
    32     34       set num {}
................................................................................
   670    672   }
   671    673   
   672    674   HEADING 1 {Choosing between multiple indices} multi_index
   673    675   
   674    676   PARAGRAPH {
   675    677     Each table in the FROM clause of a query can use at most one index
   676    678     (except when the <a href="#or_opt">OR-clause optimization</a> comes into
   677         -  paly)
          679  +  play)
   678    680     and SQLite strives to use at least one index on each table.  Sometimes,
   679    681     two or more indices might be candidates for use on a single table.
   680    682     For example:
   681    683   }
   682    684   CODE {
   683    685     CREATE TABLE ex2(x,y,z);
   684    686     CREATE INDEX ex2i1 ON ex2(x);
................................................................................
   940    942   PARAGRAPH {)^
   941    943     ^In order for these optimizations to occur, they must appear in exactly
   942    944     the form shown above - changing only the name of the table and column.
   943    945     ^It is not permissible to add a WHERE clause or do any arithmetic on the
   944    946     result.  ^The result set must contain a single column.
   945    947     ^The column in the MIN or MAX function must be an indexed column.
   946    948   }
          949  +
          950  +HEADING 1 {Automatic Indices} autoindex {automatic indexing} {Automatic indexing}
          951  +
          952  +PARAGRAPH {
          953  +  ^(When no indices are available to aid the evaulation of a query, SQLite
          954  +  will consider creating a automatic index that lasts only for the duration
          955  +  of a single SQL statement and use that index to help boost the query
          956  +  performance.)^  Since the cost of constructing the automatic index is
          957  +  O(NlogN) (where N is the number of entries in the table) and the cost of
          958  +  doing a full table scan is only O(N), an automatic index will
          959  +  only be created if SQLite expects that the lookup will be run more than
          960  +  logN times during the course of the SQL statement. Consider an example:
          961  +}
          962  +CODE {
          963  +  CREATE TABLE t1(a,b);
          964  +  CREATE TABLE t2(c,d);
          965  +  -- Insert many rows into both t1 and t2
          966  +  SELECT * FROM t1, t2 WHERE a=c;
          967  +}
          968  +PARAGRAPH {
          969  +  In the query above, if both t1 and t2 have approximately N rows, then
          970  +  without any indices the query will require O(N*N) time.  On the other
          971  +  hand, creating an index on table t2 requires O(NlogN) time and then using 
          972  +  that index to evaluate the query requires an additional O(NlogN) time.
          973  +  In the absence of [ANALYZE] information, SQLite guesses that N is one
          974  +  million and hence it believes that constructing the automatic index will
          975  +  be the cheaper approach.
          976  +}
          977  +PARAGRAPH {
          978  +  An automatic index might also be used for a subquery:
          979  +}
          980  +CODE {
          981  +  CREATE TABLE t1(a,b);
          982  +  CREATE TABLE t2(c,d);
          983  +  -- Insert many rows into both t1 and t2
          984  +  SELECT a, (SELECT d FROM t2 WHERE c=b) FROM t1;
          985  +}
          986  +PARAGRAPH {
          987  +  In this example, the t2 table is used in a subquery to translate values
          988  +  of the t1.b column.  If each table contains N rows, SQLite expects that
          989  +  the subquery will run N times, and hence it will believe it is faster
          990  +  to construct an automatic, transient index on t2 first and then using
          991  +  that index to satisfy the N instances of the subquery.
          992  +}
          993  +PARAGRAPH {
          994  +  The automatic indexing capability can be disabled at run-time using
          995  +  the [automatic_index pragma] and can be omitted from the build at
          996  +  compile-time using the [SQLITE_OMIT_AUTOMATIC_INDEX] compile-time option.
          997  +}
   947    998   </tcl>

Changes to pages/pragma.in.

    92     92   database name is omitted, "main" is assumed.  ^In some pragmas, the database
    93     93   name is meaningless and is simply ignored.</p>
    94     94   
    95     95   <tcl>Section {Pragmas to modify library operation} modify</tcl>
    96     96   </tcl>
    97     97   
    98     98   <ul>
           99  +<tcl>Subsection automatic_index</tcl>
          100  +<li><p>^(<b>PRAGMA automatic_index;
          101  +       <br>PRAGMA automatic_index = </b><i>boolean</i><b>;</b></p>
          102  +    <p>Query, set, or clear the [automatic indexing] capability.)^
          103  +
          104  +    <p>^[Automatic indexing] is enabled by default.
          105  +     ^This pragma only influences the query plan as statements are
          106  +     prepared or reprepared.  Existing prepared statements must be
          107  +     reprepared for a change in the automatic_index setting to affect
          108  +     their operation.
          109  +</li>
          110  +
    99    111   <tcl>Subsection auto_vacuum</tcl>
   100    112   <li><p><b>PRAGMA auto_vacuum;<br>
   101    113             PRAGMA auto_vacuum = </b>
   102    114              <i>0 | NONE | 1 | FULL | 2 | INCREMENTAL</i><b>;</b></p>
   103    115   
   104    116       <p>Query or set the auto-vacuum status in the database.</p>
   105    117