Documentation Source Text

proc chng {date desc {options {}}} {
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2014-06-?? (3.8.5)} {
<li>Added support for [sorting subsets of the result]
    when the result comes out of the query
    engine already partially sorted.
<li>Enhance the query planner so that it always prefers an index that uses a superset of
    WHERE clause terms relative to some other index.
<li>Improvements to the [automerge command] of [FTS4] to better control the index size
    for a full-text index that is subject to a large number of updates.
<li>Added the [SQLITE_TESTCTRL_BYTEORDER] test control.
<li>Added the [sqlite3_rtree_query_callback()] interface to [R-Tree extension]

HEADING 1 {ORDER BY optimizations} order_by

PARAGRAPH {
  ^SQLite attempts to use an index to satisfy the ORDER BY clause of a
  query when possible.
  ^When faced with the choice of using an index to satisfy WHERE clause
  constraints or satisfying an ORDER BY clause, SQLite does the same
  cost analysis described above
  and chooses the index that it believes will result in the fastest answer.
}

PARAGRAPH {
  ^SQLite will also attempt to use indices to help satisfy GROUP BY clauses
  and the DISTINCT keyword.  If the nested loops of the join can be arranged
  such that rows that are equivalent for the GROUP BY or for the DISTINCT are
  consecutive, then the GROUP BY or DISTINCT logic can determine if the 
  current row is part of the same group or if the current row is distinct
  simply by comparing the current row to the previous row.
  This can be much faster than the alternative of comparing each row to
  all prior rows.
}

HEADING 2 {Partial ORDER BY Via Index} partsort
hd_keywords {sorting subsets of the result}

PARAGRAPH {
  If a query contains an ORDER BY clause with multiple terms, it might
  be that SQLite can use indices to cause rows to come out in the order
  of some prefix of the terms in the ORDER BY but that later terms in
  the ORDER BY are not satisfied.  In that case, SQLite does block sorting.
  Suppose the ORDER BY clause has four terms and the natural order of the
  query results in rows appearing in order of the first two terms.  As
  each row is output by the query engine and enters the sorter, the 
  outputs in the current row corresponding to the first two terms of 
  the ORDER BY are comparied against the previous row.  If they have
  changed, the current sort is finished and output and a new sort is
  started.  This results in a slightly faster sort.  But the bigger
  advantages are that many fewer rows need to be held in memory,
  reducing memory requirements, and outputs can begin to appear before
  the core query has run to completion.
}

HEADING 1 {Subquery flattening} flattening
hd_keywords {flattening optimization}

PARAGRAPH {
  When a subquery occurs in the FROM clause of a SELECT, the simplest
  behavior is to evaluate the subquery into a transient table, then run

  Would be rewritten using query flattening as:
}
CODE {
  SELECT x+y AS a FROM t1 WHERE z<100 AND a>5
}
PARAGRAPH {)^
  There is a long list of conditions that must all be met in order for
  query flattening to occur.  Some of the constraints are marked as 
  obsolete by italic text.  These extra constraints are retained in the
  documentation to preserve the numbering of the other constraints.
}
PARAGRAPH {
  <ol>
  <li value="1">  ^The subquery and the outer query do not both use aggregates.

  <li value="2">
  ^The subquery is not an aggregate or the outer query is not a join.

  <li value="3">
  ^The subquery is not the right operand of a left outer join.

  <li value="4">  ^The subquery is not DISTINCT.



  <li value="5"> <i>(Subsumed into constraint 4)</i>

  <li value="6">
        ^The subquery does not use aggregates or the outer query is not
        DISTINCT.

  <li value="7">
  ^The subquery has a FROM clause.

  <li value="8">
  ^The subquery does not use LIMIT or the outer query is not a join.

  <li value="9">
  ^The subquery does not use LIMIT or the outer query does not use
  aggregates.

  <li value="10">
  ^The subquery does not use aggregates or the outer query does not
  use LIMIT.

  <li value="11">
  ^The subquery and the outer query do not both have ORDER BY clauses.

  <li value="12"> <i>(Subsumed into constraint 3)</i>

  <li value="13">  ^The subquery and outer query do not both use LIMIT.

  <li value="14">  ^The subquery does not use OFFSET.

  <li value="15">
  ^The outer query is not part of a compound select or the
  subquery does not have a LIMIT clause.

  <li value="16">
  ^The outer query is not an aggregate or the subquery does
  not contain ORDER BY. 

  <li value="17">
  ^(The sub-query is not a compound select, or it is a UNION ALL 
  compound clause made up entirely of non-aggregate queries, and 
  the parent query:

  <ul>
  <li> is not itself part of a compound select,
  <li> is not an aggregate or DISTINCT query, and
  <li> is not a join.
  </ul>)^

  ^The parent and sub-query may contain WHERE clauses. ^Subject to
  rules (11), (12) and (13), they may also contain ORDER BY,
  LIMIT and OFFSET clauses.

  <li value="18">
  ^If the sub-query is a compound select, then all terms of the
  ORDER by clause of the parent must be simple references to 
  columns of the sub-query.

  <li value="19">
  ^The subquery does not use LIMIT or the outer query does not
  have a WHERE clause.

  <li value="20">
  ^If the sub-query is a compound select, then it must not use
  an ORDER BY clause.

  <li value="21">
  ^The subquery does not use LIMIT or the outer query is not
  DISTINCT.

  <li value="22"> ^The subquery is not a recursive CTE.

  <li value="23"> ^The parent is not a recursive CTE, or the sub-query
  is not a compound query.
  </ol>
}
PARAGRAPH {
  The casual reader is not expected to understand or remember any part of
  the list above.  The point of this list is to demonstrate
  that the decision of whether or not to flatten a query is complex.
  
}
PARAGRAPH {
  Query flattening is an important optimization when views are used as
  each use of a view is translated into a subquery.
}

HEADING 1 {The MIN/MAX optimization} minmax

PARAGRAPH {
  ^(Queries that contain a single MIN() or MAX() aggregate function whose
  argument is the left-most column of an index might be satisfied
  by doing a single index lookup rather than by scanning the entire table.)^
  Examples:
}
CODE {
  SELECT MIN(x) FROM table;
  SELECT MAX(x)+1 FROM table;







}

HEADING 1 {Automatic Indices} autoindex {automatic indexing} {Automatic indexing}

PARAGRAPH {
  ^(When no indices are available to aid the evaluation of a query, SQLite
  might create an automatic index that lasts only for the duration