Documentation Source Text

Check-in [f6c99b3cc3]
Login

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

Overview
Comment:Change fts3.html to present the new INSERT syntax used to optimize the full-text index.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f6c99b3cc3ba672a54d6da233fa49d071dfd65df
User & Date: dan 2009-12-11 14:46:47
Context
2009-12-11
16:33
Fix a typo in the google url on the famous.html page. check-in: 66c988ccf9 user: drh tags: trunk
14:46
Change fts3.html to present the new INSERT syntax used to optimize the full-text index. check-in: f6c99b3cc3 user: dan tags: trunk
14:20
Comment out the line in althttpd that disallows the msnbot. We are going to give Bing a second chance.... check-in: 101a84e46c user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/fts3.in.

195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
    complete description of the \[segment btree|data structure\] used to store
    this index within the database file is described below. A feature of
    this data structure is that at any time the database may contain not
    one index b-tree, but several different b-trees that are incrementally
    merged as rows are inserted, updated and deleted. This technique improves 
    performance when writing to an FTS3 table, but causes some overhead for
    full-text queries that use the index. Executing an SQL statement of the
    form "SELECT optimize(<fts3-table>) FROM <fts3-table>" 
    causes FTS3 to merge all existing index b-trees into a single large
    b-tree containing the entire index. This can be an expensive operation,
    but may speed up future queries. 

  <p>
    For example, to optimize the full-text index for an FTS3 table named
    "docs":

[Code {
  <i>-- Optimize the internal structure of FTS3 table "docs".</i>
  SELECT optimize(docs) FROM docs LIMIT 1;
}]

  <p>
    The statement above may appear syntacticly incorrect to some. Refer to
    the section describing the \[simple fts3 queries\] for an explanation.

  <p>
    The optimize() function returns a text value. If the index was already
    optimized when it was called the text is "Index already optimal". 
    Otherwise if the index was not already optimized, it is made so and
    the text "Index optimized" returned.

[h2 "Querying FTS3 Tables" {} {simple fts3 queries}]

<p>
  As for all other SQLite tables, virtual or otherwise, data is retrieved
  from FTS3 tables using a \[SELECT\] statement.








|










|







|
|
|
<







195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223

224
225
226
227
228
229
230
    complete description of the \[segment btree|data structure\] used to store
    this index within the database file is described below. A feature of
    this data structure is that at any time the database may contain not
    one index b-tree, but several different b-trees that are incrementally
    merged as rows are inserted, updated and deleted. This technique improves 
    performance when writing to an FTS3 table, but causes some overhead for
    full-text queries that use the index. Executing an SQL statement of the
    form "INSERT INTO &lt;fts3-table&gt;(&lt;fts3-table&gt;) VALUES('optimize')"
    causes FTS3 to merge all existing index b-trees into a single large
    b-tree containing the entire index. This can be an expensive operation,
    but may speed up future queries. 

  <p>
    For example, to optimize the full-text index for an FTS3 table named
    "docs":

[Code {
  <i>-- Optimize the internal structure of FTS3 table "docs".</i>
  INSERT INTO docs(docs) VALUES('optimize');
}]

  <p>
    The statement above may appear syntacticly incorrect to some. Refer to
    the section describing the \[simple fts3 queries\] for an explanation.

  <p>
    There is another, deprecated, method for invoking the optimize 
    operation using a SELECT statement. New code should use statements
    similar to the INSERT above to optimize FTS3 structures.


[h2 "Querying FTS3 Tables" {} {simple fts3 queries}]

<p>
  As for all other SQLite tables, virtual or otherwise, data is retrieved
  from FTS3 tables using a \[SELECT\] statement.