Documentation Source Text

Check-in [7c122e0975]
Login

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

Overview
Comment:Update the details of when the LIKE optimization is allowed, due to enhancements to the LIKE optimization logic in 3.18.0.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7c122e0975b8266a650511eb2d5371f0c53ae397
User & Date: drh 2017-03-15 20:51:16
Context
2017-03-15
23:10
An initial top-level README.md file. check-in: 1b99dd4dc8 user: drh tags: trunk
20:51
Update the details of when the LIKE optimization is allowed, due to enhancements to the LIKE optimization logic in 3.18.0. check-in: 7c122e0975 user: drh tags: trunk
20:32
Further refinement to the loadable extension documentation. check-in: cecf106465 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/optoverview.in.

309
310
311
312
313
314
315
316
317


318
319
320
321
322
323
324
325
326
327











328
329
330
331
332
333
334
  high-level overview of the chosen query strategy.
}

HEADING 1 {The LIKE optimization} like_opt
hd_keywords {LIKE optimization}

PARAGRAPH {
  Terms that are composed of the [LIKE] or [GLOB] operator
  can sometimes be used to constrain indices.


  There are many conditions on this use:
}
PARAGRAPH {
  <ol>
  <li>^The left-hand side of the LIKE or GLOB operator must be the name
      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 built-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







|
|
>
>
|



<
<




>
>
>
>
>
>
>
>
>
>
>







309
310
311
312
313
314
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
  high-level overview of the chosen query strategy.
}

HEADING 1 {The LIKE optimization} like_opt
hd_keywords {LIKE optimization}

PARAGRAPH {
  A WHERE-clause term that uses the [LIKE] or [GLOB] operator
  can sometimes be used with an index to do a range search, 
  almost as if the LIKE or GLOB were an alternative to a [BETWEEN]
  operator.
  There are many conditions on this optimization:
}
PARAGRAPH {
  <ol>


  <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>It must not be possible to make the LIKE or GLOB operator true by
      having a numeric value (instead of a string or blob) on the
      left-hand side. This means that either:
      <ol type="A">
      <li> the left-hand side of the LIKE or GLOB operator is the name
           of an indexed column with [affinity | TEXT affinity], or</li>
      <li> the right-hand side pattern argument does not begin with a
           minus sign ("-") or a digit.</li>
      </ol>
      This constraint arises from the fact that numbers do not sort in
      lexicographical order.  For example: 9&lt;10 but '9'&gt;'10'.</li>
  <li>^The built-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