Intro.` FTS3 enables full-text searching in SQLite. We use SQLite to index all the words in a TEXT column in an SQLite3 database. We get started with the FTS3 feature of SQLite.`Some results.` The results of using FT3 in a small database were impressive. After removing stop words, the search was done much faster. And the index was smaller. `The database contained more than 700 text documents. It was reduced by not indexing stop words.`First,` we make a virtual table. A FTS3 table is a virtual table with extensions enabled in SQLite. After we use FTS3, we will see some ideas of how its performance can be improved.`You can create` a virtual FTS3 table using SQL. You can store other kinds of columns in SQLite virtual tables, but only the TEXT ones are searched with FTS3 and the MATCH syntax. `Update FTS3 table.` Here we look at updating FTS3 tables. Remember that before you are able to update data into the virtual table, you need to insert rows into it. `With SQLite there is no way around checking whether you need to insert or update a row. Here's how to update.`Description of the UPDATE statement.` You need to select first. Select first to see if the row exists, and you can insert as usual if it isn't there. `FTS3 is like a regular table. The virtual table acts like a regular table, but you need to manually maintain it.`You must maintain the indexes the virtual table to maintain referential integrity. You need to UPDATE the FTS3 table.`Match.` Here we use MATCH to search through full-text tables in SQLite. Make sure you use MATCH instead of equals or LIKE to scan a TEXT column in a virtual table. `We find` a row that contains the query, and then we take the date from that row in the FTS3 table. Then we do a join and find the row in the other table that has more information. `Details: `This "links" the virtual table with the main table through a surrogate key.`This syntax is similar to LIKE or WHERE syntaxes, so it won't be a problem to understand.`Row ID.` In SQLite and other databases, each row has a unique identifier called a rowid. You don't need to tell the database to create this. The rowid gives us more power. `Select rowid.` You have a main table, which contains content and data, and a FTS3 virtual table. Search text using MATCH on the virtual table, and then get the corresponding row content. `Join rowid.` To optimize performance and improve code clarity, minimize the trips to the SQLite database. Use an implicit join on the two tables using rowid, an int. `This query` selects from both tables at once, and names them dt and vt. Next it finds all ContentCol where the query is present and then does an implicit join on the two tables. `Finally: `It returns the full content that you want to give the user instead of the virtual table contents.`Optimize.` My database grew to three times its previous size when I enabled FTS3. Stopwords are short, common words that are not indexed. These make searching slower. `Stopword Dictionary `stopword-dictionary`We used` FTS3 in SQLite 3 for fast searching. SQLite does this well. The results of FTS3 implementations in my programs were impressive.
#$%Optimization $s: FTS3% No FTS3 enabled: % 7.0 MB% [smallest] Unreduced FTS3: %19.0 MB% [largest] Reduced FTS3:#% 9.0 MB%C$ FTS3 table% %-- C$ a fts3 table that c$ two columns. -- The first is a text column$the second is a datetime column. -- The column names$table name must be customized. %CREATE VIRTUAL TABLE TableName #USING FTS3(ColOne TEXT, ColTwo DATETIME)%UPDATE% %-- Update our virtual table. %UPDATE TableName #SET ColOne=@a #WHERE ColTwo=@b%MATCH% %-- Look at the line starting with MATCH. -- That's where the fts3 stuff takes effect. -- FullText is a fts3 virtual table. %SELECT jt.EntryTitle, jt.$New, jt.Word$ #FROM JournalTable jt, FullText ft #WHERE ft.FullContent #MATCH @query #AND jt.$New$ft.FullDate #ORDER BY ft.FullDate DESC #LIMIT 100%SELECT% %-- $some matches from the fts3 table, then select the rowid. %SELECT rowid #FROM VirtualTable #WHERE ContentCol #MATCH @query #LIMIT @limit #OFFSET @offset;%SELECT e$ 2% %-- Fetch the relevant row from the other table. %SELECT ColA, ColB #FROM DataTable #WHERE rowid=@id;%SELECT e$ 3% %-- Join virtual table with regular table on rowid. %SELECT dt.ColA, dt.ColB #FROM DataTable dt, VirtualTable vt #WHERE vt.ContentCol #MATCH @query AND dt.rowid$vt.rowid #LIMIT @limit #OFFSET @offset;%