Documentation Source Text

  are supported:

<ul>
<li><p>INSERT INTO xyz(xyz) VALUES('optimize');</p>
<li><p>INSERT INTO xyz(xyz) VALUES('rebuild');</p>
<li><p>INSERT INTO xyz(xyz) VALUES('integrity-check');</p>
<li><p>INSERT INTO xyz(xyz) VALUES('merge=X,Y');</p>
<li><p>INSERT INTO xyz(xyz) VALUES('automerge=N');</p>
</ul>

<tcl>hd_fragment *fts4optcmd {FTS4 "optimize" command} \
                             {"optimize" command}</tcl>
<h2 id=optimize>The "optimize" command</h2>

<p>

  for X in the range of 100 to 300.  The idle thread that is running
  the merge commands can know when it is done by checking the difference
  in [sqlite3_total_changes()] before and after each "merge=X,Y"
  command and stopping the loop when the difference drops below two.

<tcl>hd_fragment *fts4automergecmd {FTS4 "automerge" command} \
                                   {"automerge" command}</tcl>
<h2 id=automerge">The "automerge=N" command</h2>

<p>
  The "automerge=N" command (where N is an integer between 0 and 15,
  inclusive) is used to configure an FTS3/4 tables "automerge" parameter,
  which controls automatic incremental inverted index merging. The default 
  automerge value for new tables is 0, meaning that automatic incremental 
  merging is completely disabled. If the value of the automerge parameter
  is modified using the "automerge=N" command, the new parameter value is
  stored persistently in the database and is used by all subsequently
  established database connections.

<p>
  Setting the automerge parameter to a non-zero value enables automatic
  incremental merging. This causes SQLite to do a small amount of inverted 
  index merging after every INSERT operation. The amount of merging 
  performed is designed so that the FTS3/4 table never reaches a point 
  where it has 16 segments at the same level and hence has to do a large 

  merge in order to complete an insert.  In other words, automatic 
  incremental merging is designed to prevent spiky INSERT performance.

<p>
  The downside of automatic incremental merging is that it makes
  every INSERT, UPDATE, and DELETE operation on an FTS3/4 table run
  a little slower, since extra time must be used to do the incremental
  merge.  For maximum performance, it is recommended that applications
  disable automatic incremental merge and instead use the 
  ["merge" command] in an idle process to keep the inverted indices
  well merged.  But if the structure of an application does not easily
  allow for idle processes, the use of automatic incremental merge is
  a very reasonable fallback solution.

<p>
  The actual value of the automerge parameter determines the number of
  index segments merged simultaneously by an automatic inverted index
  merge. If the value is set to N, the system waits until there are at
  least N segments on a single level before beginning to incrementally
  merge them. Setting a lower value of N causes segments to be merged more
  quickly, which may speed up full-text queries and, if the workload 
  contains UPDATE or DELETE operations as well as INSERTs, reduce the space
  on disk consumed by the full-text index. However, it also increases the
  amount of data written to disk.

<p>
  For general use in cases where the workload contains few UPDATE or DELETE
  operations, is 8. If the workload contains many UPDATE or DELETE commands, 
  or if query speed is a concern, it may be advantageous to reduce it to 2.

<p>
  For reasons of backwards compatibility, the "automerge=1" command sets
  the automerge parameter to 8, not 1 (a value of 1 would make no sense 
  anyway, as merging data from a single segment is a no-op).


<h1 id=tokenizer tags="tokenizer">Tokenizers</h1>

<p>
  An FTS tokenizer is a set of rules for extracting terms from a document 
  or basic FTS full-text query. 

    belongs to this segment b-tree. Or zero if the entire segment b-tree
    fits on the root node. If it exists, this node is always a leaf node.
  <tr><td>leaves_end_block <td>
    The blockid that corresponds to the leaf node with the largest blockid 
    that belongs to this segment b-tree. Or zero if the entire segment b-tree
    fits on the root node.
  <tr><td>end_block <td>
    This field may contain either an integer or a text field consisting of
    two integers separated by a space character (unicode codepoint 0x20).
<p style="margin-left:0;margin-right:0">
    The first, or only, integer is the blockid that corresponds to the interior
    node with the largest blockid that belongs to this segment b-tree. Or zero
    if the entire segment b-tree fits on the root node. If it exists, this node
    is always an interior node.
<p style="margin-left:0;margin-right:0;margin-bottom:0">
    The second integer, if it is present, is the aggregate size of all data
    stored on leaf pages in bytes. If the value is negative, then the segment
    is the output of an unfinished incremental-merge operation, and the
    absolute value is current size in bytes.

  <tr><td>root             <td>
    Blob containing the root node of the segment b-tree.
</table>

<p>
  Apart from the root node, the nodes that make up a single segment b-tree are
  always stored using a contiguous sequence of blockids. Furthermore, the