Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Updates to the optimizer documentation. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
06b1be36dbf5a0935b067ab76bd6f89a |
User & Date: | drh 2010-07-23 01:13:55.000 |
Context
2010-07-26
| ||
11:58 | Remove <dl> marks from requirements text prior to insertion into the matrix. (check-in: 9b8e4462b6 user: drh tags: trunk) | |
2010-07-23
| ||
01:13 | Updates to the optimizer documentation. (check-in: 06b1be36db user: drh tags: trunk) | |
2010-07-22
| ||
01:47 | Fix a typo (reported on the sqlite-users mailing list) in the history for release 3.6.18. (check-in: e0993fbf96 user: drh tags: trunk) | |
Changes
Changes to pages/optoverview.in.
︙ | ︙ | |||
61 62 63 64 65 66 67 | } HEADING 1 {WHERE clause analysis} where_clause PARAGRAPH { ^The WHERE clause on a query is broken up into "terms" where each term is separated from the others by an AND operator. | | | | | | | | | | | | 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 | } HEADING 1 {WHERE clause analysis} where_clause PARAGRAPH { ^The WHERE clause on a query is broken up into "terms" where each term is separated from the others by an AND operator. If the WHERE clause is composed of constraints separate by the OR operator then the entire clause is considered to be a single "term" to which the <a href="#or_opt">OR-clause optimization</a> is applied. } PARAGRAPH { ^All terms of the WHERE clause are analyzed to see if they can be satisfied using indices. ^Terms that cannot be satisfied through the use of indices become tests that are evaluated against each row of the relevant input tables. ^No tests are done for terms that are completely satisfied by indices. ^Sometimes one or more terms will provide hints to indices but still must be evaluated against each row of the input tables. } PARAGRAPH { ^The analysis of a term might cause new "virtual" terms to be added to the WHERE clause. ^Virtual terms can be used with indices to restrict a search. ^But virtual terms never generate code that is tested against input rows. } PARAGRAPH { ^(To be usable by an index a term must be of one of the following forms: } SYNTAX { /column/ = /expression/ /column/ > /expression/ /column/ >= /expression/ /column/ < /expression/ /column/ <= /expression/ /expression/ = /column/ /expression/ > /column/ /expression/ >= /column/ /expression/ < /column/ /expression/ <= /column/ /column/ IN (/expression-list/) /column/ IN (/subquery/) /column/ IS NULL } PARAGRAPH {)^ ^(If an index is created using a statement like this: } CODE { CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z); } PARAGRAPH { Then the index might be used if the initial columns of the index |
︙ | ︙ | |||
266 267 268 269 270 271 272 | } SYNTAX { rowid IN (SELECT rowid FROM /table/ WHERE /expr1/ UNION SELECT rowid FROM /table/ WHERE /expr2/ UNION SELECT rowid FROM /table/ WHERE /expr3/) } PARAGRAPH { | | | | | | 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 | } SYNTAX { rowid IN (SELECT rowid FROM /table/ WHERE /expr1/ UNION SELECT rowid FROM /table/ WHERE /expr2/ UNION SELECT rowid FROM /table/ WHERE /expr3/) } PARAGRAPH { The rewritten expression above is conceptual; WHERE clauses containing OR are not really rewritten this way. The actual implemention of the OR clause uses a mechanism that is more efficient than subqueries and which works even for tables where the "rowid" column name has been overloaded for other uses and no longer refers to the real rowid. But the essence of the implementation is captured by the statement above: Separate indices are used to find candidate result rows from each OR clause term and the final result is the union of those rows. } PARAGRAPH { Note that in most cases, SQLite will only use a single index for each table in the FROM clause of a query. The second OR-clause optimization |
︙ | ︙ | |||
315 316 317 318 319 320 321 | of an indexed column with [affinity | TEXT affinity].</li> <li>^The right-hand side of the LIKE or GLOB must be either a string literal or a [parameter] bound to a string literal that does not begin with a wildcard character.</li> <li>^The ESCAPE clause cannot appear on the LIKE operator.</li> <li>^The build-in functions used to implement LIKE and GLOB must not have been overloaded using the sqlite3_create_function() API.</li> | | | | | | | | | | | < < < | 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 | of an indexed column with [affinity | TEXT affinity].</li> <li>^The right-hand side of the LIKE or GLOB must be either a string literal or a [parameter] bound to a string literal that does not begin with a wildcard character.</li> <li>^The ESCAPE clause cannot appear on the LIKE operator.</li> <li>^The build-in functions used to implement LIKE and GLOB must not have been overloaded using the sqlite3_create_function() API.</li> <li>^For the GLOB operator, the column must be indexed using the built-in BINARY collating sequence.</li> <li>^For the LIKE operator, if [case_sensitive_like] mode is enabled then the column must indexed using BINARY collating sequence, or if [case_sensitive_like] mode is disabled then the column must indexed using built-in NOCASE collating sequence.</li> </ol> } PARAGRAPH { The LIKE operator has two modes that can be set by a [case_sensitive_like | pragma]. ^The default mode is for LIKE comparisons to be insensitive to differences of case for latin1 characters. ^(Thus, by default, the following expression is true: } CODE { 'a' LIKE 'A' } PARAGRAPH {)^ ^(But if the case_sensitive_like pragma is enabled as follows: } CODE { PRAGMA case_sensitive_like=ON; } PARAGRAPH { Then the LIKE operator pays attention to case and the example above would evaluate to false.)^ ^Note that case insensitivity only applies to latin1 characters - basically the upper and lower case letters of English in the lower 127 byte codes of ASCII. ^International character sets are case sensitive in SQLite unless a application-defined [collating sequence] and [like | like() SQL function] are provided that take non-ASCII characters into account. ^But if an application-defined collating sequence and/or like() SQL function are provided, the LIKE optimization described here will never be taken. } PARAGRAPH { The LIKE operator is case insensitive by default because this is what the SQL standard requires. You can change the default behavior at compile time by using the [SQLITE_CASE_SENSITIVE_LIKE] command-line option to the compiler. } PARAGRAPH { ^(The LIKE optimization might occur if the column named on the left of the operator is indexed using the built-in BINARY collating sequence and case_sensitive_like is turned on. Or the optimization might occur if the column is indexed using the built-in NOCASE collating sequence and the case_sensitive_like mode is off. These are the only two combinations under which LIKE operators will be optimized.)^ } PARAGRAPH { ^The GLOB operator is always case sensitive. ^The column on the left side of the GLOB operator must always use the built-in BINARY collating sequence or no attempt will be made to optimize that operator with indices. } PARAGRAPH { |
︙ | ︙ |