sqlite全文索引，牛！

浏览数：142 / 时间：2015年06月12日

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 */

-- The examples in this block assume the following FTS table:

CREATE VIRTUAL TABLE mail USING fts3(subject, body); 
SELECT * FROM mail WHERE rowid = 15; -- Fast. Rowid lookup. 
SELECT * FROM mail WHERE body MATCH ‘sqlite‘; -- Fast. Full-text query. 
SELECT * FROM mail WHERE mail MATCH ‘search‘; -- Fast. Full-text query. 
SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20; -- Slow. Linear scan. 
SELECT * FROM mail WHERE subject = ‘database‘; -- Slow. Linear scan. 
SELECT * FROM mail WHERE subject MATCH ‘database‘; -- Fast. Full-text query.

The Snippet Function

The snippet function is used to create formatted fragments of document text for display as part of a full-text query results report. The snippet function may be passed between one and six arguments, as follows:

Argument	Default Value	Description
0	N/A	The first argument to the snippet function must always be the FTS hidden column of the FTS table being queried and from which the snippet is to be taken. The FTS hidden column is an automatically generated column with the same name as the FTS table itself.
1	"<b>"	The "start match" text.
2	"</b>"	The "end match" text.
3	"<b>...</b>"	The "ellipses" text.
4	-1	The FTS table column number to extract the returned fragments of text from. Columns are numbered from left to right starting with zero. A negative value indicates that the text may be extracted from any column.
5	-15	The absolute value of this integer argument is used as the (approximate) number of tokens to include in the returned text value. The maximum allowable absolute value is 64. The value of this argument is referred to as N in the discussion below.