/ Check-in [6ec8883c]
Login

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

Overview
Comment:Documentation updates. (CVS 2726)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6ec8883c366532948e619830ff7d9f63b79fc08b
User & Date: drh 2005-09-20 01:36:30
Context
2005-09-20
08:47
Update comments in where.c. No code changes. (CVS 2727) check-in: bb84d27e user: drh tags: trunk
01:36
Documentation updates. (CVS 2726) check-in: 6ec8883c user: drh tags: trunk
2005-09-19
21:05
ON-clause terms in a LEFT JOIN that restrict only the left table, should not really restrict the left table but instead rows that fail to meet the condition to be joined with NULL rows from the right table. (CVS 2725) check-in: ea10f978 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to www/optoverview.tcl.

1
2
3
4
5
6
7
8
9
10
11
..
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
...
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
...
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
...
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
...
331
332
333
334
335
336
337








338
339
340
341
342
343
344
#
# Run this TCL script to generate HTML for the goals.html file.
#
set rcsid {$Id: optoverview.tcl,v 1.3 2005/08/31 13:48:35 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 "/(\[^\n/\]+)/" $text {<i>\1</i>} t2
  regsub -all "\\*(\[^\n*\]+)\\*" $t2 {<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 /expr1/ 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 /column/ 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 /x/.  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 /y/ the smallest string that is the same length as /x/ but which
  compares greater than /x/.  For example, if /x/ is *hello* then
  /y/ 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 /x/ prefix.  However, if there is only a single global wildcard
  to the right of /x/, 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.
}









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.



|







 







|
|







 







|







 







|







 







|


|
|
|











|
|







 







>
>
>
>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
..
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
...
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
...
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
...
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
...
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
#
# 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.