SQLite

#
# Run this TCL script to generate HTML for the goals.html file.
#
set rcsid {$Id: optoverview.tcl,v 1.4 2005/09/20 01:36:30 drh Exp $}
source common.tcl
header {The SQLite Query Optimizer Overview}

proc CODE {text} {
  puts "<blockquote><pre>"
  puts $text
  puts "</pre></blockquote>"

  puts "<center><img src=\"$name\">"
  if {$caption!=""} {
    puts "<br>$caption"
  }
  puts "</center>"
}
proc PARAGRAPH {text} {
  # regsub -all "/(\[a-zA-Z0-9\]+)/" $text {<i>\1</i>} t2
  regsub -all "\\*(\[^\n*\]+)\\*" $text {<tt><b><big>\1</big></b></tt>} t3
  puts "<p>$t3</p>\n"
}
set level(0) 0
set level(1) 0
proc HEADING {n name {tag {}}} {
  if {$tag!=""} {
    puts "<a name=\"$tag\">"

  is not performed on input rows.
  Thus if the BETWEEN term ends up being used as an index constraint
  no tests are ever performed on that term.
  On the other hand, the
  virtual terms themselves never causes tests to be performed on
  input rows.
  Thus if the BETWEEN term is not used as an index constraint and
  instead must be used to test input rows, the <i>expr1</i> expression is
  only evaluated once.
}

HEADING 1 {The OR optimization} or_opt

PARAGRAPH {
  If a term consists of multiple subterms containing a common column

}
SYNTAX {
  /column/ IN (/expr1/,/expr2/,/expr3/,/expr4/,...)
}
PARAGRAPH {
  The rewritten term then might go on to constraint an index using the
  normal rules for *IN* operators.
  Note that <i>column</i> must be the same column in every OR-connected subterm,
  although the column can occur on either the left or the right side of
  the *=* operator.
}

HEADING 1 {The LIKE optimization} like_opt

PARAGRAPH {

  value that does not begin with a wildcard.  If the right-hand side is a
  parameter that is bound to a string, then no optimization is attempted.
  If the right-hand side begins with a wildcard character then no 
  optimization is attempted.
}
PARAGRAPH {
  Suppose the initial sequence of non-wildcard characters on the right-hand
  side of the LIKE or GLOB operator is <i>x</i>.  We are using a single 
  character to denote this non-wildcard prefix but the reader should
  understand that the prefix can consist of more than 1 character.
  Let <i>y</i> the smallest string that is the same length as /x/ but which
  compares greater than <i>x</i>.  For example, if <i>x</i> is *hello* then
  <i>y</i> would be *hellp*.
  The LIKE and GLOB optimizations consist of adding two virtual terms
  like this:
}
SYNTAX {
  /column/ >= /x/ AND /column/ < /y/
}
PARAGRAPH {
  Under most circumstances, the original LIKE or GLOB operator is still
  tested against each input row even if the virtual terms are used to
  constrain an index.  This is because we do not know what additional
  constraints may be imposed by characters to the right
  of the <i>x</i> prefix.  However, if there is only a single global wildcard
  to the right of <i>x</i>, then the original LIKE or GLOB test is disabled.
  In other words, if the pattern is like this:
}
SYNTAX {
  /column/ LIKE /x/%
  /column/ GLOB /x/*
}
PARAGRAPH {

  The ON and USING clauses of a join are converted into additional
  terms of the WHERE clause prior to WHERE clause analysis described
  above in paragraph 1.0.  Thus
  with SQLite, there is no advantage to use the newer SQL92 join syntax
  over the older SQL89 comma-join syntax.  They both end up accomplishing
  exactly the same thing.
}
PARAGRAPH {
  Join reordering is automatic and usually works well enough that
  programmer do not have to think about it.  But occasionally some
  hints from the programmer are needed.  For a description of when
  hints might be necessary and how to provide those hints, see the
  <a href="http://www.sqlite.org/cvstrac/wiki?p=QueryPlans">QueryPlans</a>
  page in the Wiki.
}

HEADING 1 {Choosing between multiple indices} multi_index

PARAGRAPH {
  Each table in the FROM clause of a query can use at most one index,
  and SQLite strives to use at least one index on each table.  Sometimes,
  two or more indices might be candidates for use on a single table.