Documentation Source Text

Check-in [316be08907]
Login

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

Overview
Comment:Fix typos in queryplaner.html
Timelines: family | ancestors | descendants | both | branch-3.10
Files: files | file ages | folders
SHA1: 316be089075659eae75ca4782ee6ca6ed7f68d4a
User & Date: drh 2016-01-14 16:10:04
Context
2016-01-14
17:58
Fix another typo in the query planner document. check-in: 47810e77ef user: drh tags: branch-3.10
16:10
Fix typos in queryplaner.html check-in: 316be08907 user: drh tags: branch-3.10
2016-01-13
21:46
Add the release hashes to the change log for 3.10.1. check-in: 9c12cf4cea user: drh tags: branch-3.10
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/queryplanner.in.

1
2
3
4




5
6
7
8
9
10
11
...
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
...
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
...
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
<title>Query Planning</title>
<tcl>
hd_keywords {indexing} {indexing tutorial}
proc figure {fignum tag img title} {




  hd_puts "<p><center>\n"
  hd_puts "<img src=\"images/qp/$img\" alt=\"figure $fignum\"><br>\n"
  hd_puts "Figure $fignum: $title\n"
  hd_puts "</center></p>\n"
}
proc code {txt} {
  hd_puts "<center><table><tr><td><pre>\n"
................................................................................

<tcl>code {
SELECT price FROM fruitsforsale WHERE rowid=4;
}</tcl>

<p>
Since the information is stored in the table in rowid order, SQLite
can find the correct row using doing a binary search on the rowid.
If the table contains N element, the time required to look up the
desired row is proportional to logN rather than being proportional
to N as in a full table scan.  If the table contains 10 million elements,
that means the query will be on the order of N/logN or about 1 million
times faster!
</p>

................................................................................

<p>
To make the original query more efficient, we can add an index on the
"fruit" column of the "fruitsforsale" table like this:
</p>

<tcl>code {
CREATE INDEX idx1 ON fruitsforsale(fruit);
}</tcl>

<p>
An index is another table similar to the original "fruitsforsale" table
but with the content (the fruit column in this case) stored in front of the
rowid and with all rows in content order.
<a href="#fig4">Figure 4</a> gives a logical view of the Idx1 index.
................................................................................
</tcl>

<p>
The "state" index works just like the "fruit" index in that it is a
new table with an extra column in front of the rowid and sorted by
that extra column as the primary key.  The only difference is that
in Idx2, the first column is "state" instead of "fruit" as it is with
Idx1.  In our example data set, the is more redundancy in the "state"
column and so they are more duplicate entries.  The ties are still
resolved using the rowid.
</p>

<p>
Using the new Idx2 index on "state", SQLite has another option for
lookup up the price of California oranges:  it can look up every row




>
>
>
>







 







|







 







|







 







|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
...
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
...
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
...
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
<title>Query Planning</title>
<tcl>
hd_keywords {indexing} {indexing tutorial}
proc figure {fignum tag img title} {
  if {$tag!=""} {
    set tag [string trim $tag #]
    hd_puts "<a name='$tag'></a>\n"
  }
  hd_puts "<p><center>\n"
  hd_puts "<img src=\"images/qp/$img\" alt=\"figure $fignum\"><br>\n"
  hd_puts "Figure $fignum: $title\n"
  hd_puts "</center></p>\n"
}
proc code {txt} {
  hd_puts "<center><table><tr><td><pre>\n"
................................................................................

<tcl>code {
SELECT price FROM fruitsforsale WHERE rowid=4;
}</tcl>

<p>
Since the information is stored in the table in rowid order, SQLite
can find the correct row using a binary search on the rowid.
If the table contains N element, the time required to look up the
desired row is proportional to logN rather than being proportional
to N as in a full table scan.  If the table contains 10 million elements,
that means the query will be on the order of N/logN or about 1 million
times faster!
</p>

................................................................................

<p>
To make the original query more efficient, we can add an index on the
"fruit" column of the "fruitsforsale" table like this:
</p>

<tcl>code {
CREATE INDEX Idx1 ON fruitsforsale(fruit);
}</tcl>

<p>
An index is another table similar to the original "fruitsforsale" table
but with the content (the fruit column in this case) stored in front of the
rowid and with all rows in content order.
<a href="#fig4">Figure 4</a> gives a logical view of the Idx1 index.
................................................................................
</tcl>

<p>
The "state" index works just like the "fruit" index in that it is a
new table with an extra column in front of the rowid and sorted by
that extra column as the primary key.  The only difference is that
in Idx2, the first column is "state" instead of "fruit" as it is with
Idx1.  In our example data set, there is more redundancy in the "state"
column and so they are more duplicate entries.  The ties are still
resolved using the rowid.
</p>

<p>
Using the new Idx2 index on "state", SQLite has another option for
lookup up the price of California oranges:  it can look up every row