The Full-Text Search (FTS) in SQLite

sexevil

　　http://www.sqlite.org/fts3.html

　　
FTS3 and FTS4 are an SQLite virtual table modules that allows users to perform
full-text searches on a set of documents. The most common (and effective)
way to describe full-text searches is "what Google, Yahoo and Altavista do
with documents placed on the World Wide Web". Users input a term, or series
of terms, perhaps connected by a binary operator or grouped together into a
phrase, and the full-text query system finds the set of documents that best
matches those terms considering the operators and groupings the user has
specified. This article describes the deployment and usage of FTS3 and FTS4.

　　
FTS1 and FTS2 are obsolete full-text search modules for SQLite.  There are known
issues with these older modules and their use should be avoided.
Portions of the original FTS3 code were contributed to the SQLite project
by Scott Hess of Google
. It is now
developed and maintained as part of SQLite.

　　
The FTS3 and FTS4 extension modules allows users to create special tables with a
built-in full-text index (hereafter "FTS tables"). The full-text index
allows the user to efficiently query the database for all rows that contain
one or more words (hereafter "tokens"), even if the table
contains many large documents.

　　
For example, if each of the 517430 documents in the
"Enron E-Mail Dataset
"
is inserted into both an FTS table and an ordinary SQLite table
created using the following SQL script:

CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT);     /* FTS3 table */ CREATE TABLE enrondata2(content TEXT);                        /* Ordinary table */

　　
Then either of the two queries below may be executed to find the number of
documents in the database that contain the word "linux" (351). Using one
desktop PC hardware configuration, the query on the FTS3 table returns in
approximately 0.03 seconds, versus 22.5 for querying the ordinary table.

SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux';  /* 0.03 seconds */ SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */