Searching text

In short: Full-text search finds words in text through an inverted index that stores, for each word, the rows that contain it, instead of reading everything with LIKE.

Problem

The search box

The site gets a search box for the reviews. The first idea is LIKE. A customer looks for "ice":

SQLdatabase: shop

SELECT id, body FROM reviews WHERE body LIKE '%ice%';

SELECT id, body FROM reviews WHERE body LIKE '%ice%';

▶ RunRestore the queryFresh database

It finds "nicely", because LIKE looks for a piece of the text, not a word. Another search:

SQLdatabase: shop

SELECT id, body FROM reviews WHERE body LIKE '%knife%';

SELECT id, body FROM reviews WHERE body LIKE '%knife%';

▶ RunRestore the queryFresh database

Nothing, although there is a review about the knives: the text says "knives", not "knife". And % at the start rules out an index: with a million reviews the database reads a million texts for every search.

More precisely, if you are curious

In SQLite LIKE ignores the difference between capital and small letters only for the Latin alphabet. In German LIKE '%öl%' misses "Öl" (oil), and the same happens in Greek or Bulgarian. The full-text index below makes all letters small, including those.

The idea

The inverted index

Full-text search does the opposite of the table. The table says "review 3 contains the words the, best, coffee, in, the, neighbourhood". The inverted index says "the word coffee is in reviews 3, 6 and 13". Searching for a word is one read in the index, not a pass through the texts.

The text is first split into words (a tokenizer). unicode61 cuts at spaces and punctuation and makes all letters small, in every alphabet. SQLite keeps the index in a virtual table with fts4:

SQLdatabase: shop

CREATE VIRTUAL TABLE review_search USING fts4(body, tokenize=unicode61);
INSERT INTO review_search (docid, body) SELECT id, body FROM reviews;

SELECT docid, body FROM review_search WHERE review_search MATCH 'ice';

CREATE VIRTUAL TABLE review_search USING fts4(body, tokenize=unicode61); INSERT INTO review_search (docid, body) SELECT id, body FROM reviews; SELECT docid, body FROM review_search WHERE review_search MATCH 'ice';

▶ RunRestore the queryFresh database

docid is the number of the row in the index. Here it is the number of the review, so that it joins with reviews. MATCH is the search in the index, not = or LIKE. And "nicely" is not found, because "ice" is not a word in it.

This is what the words in the index look like (fts4aux is a table that shows the words and how many documents each is in):

SQLdatabase: shop

CREATE VIRTUAL TABLE review_terms USING fts4aux(review_search);

SELECT term, documents
FROM review_terms
WHERE col = '*' AND term LIKE 'c%'
ORDER BY term;

CREATE VIRTUAL TABLE review_terms USING fts4aux(review_search); SELECT term, documents FROM review_terms WHERE col = '*' AND term LIKE 'c%' ORDER BY term;

▶ RunRestore the queryFresh database

The idea

Word forms

In the index "knife" and "knives" are two different words. The simplest help is a prefix: kni* means any word that starts with "kni":

SQLdatabase: shop

SELECT docid, body FROM review_search WHERE review_search MATCH 'knife';
SELECT docid, body FROM review_search WHERE review_search MATCH 'kni*';

SELECT docid, body FROM review_search WHERE review_search MATCH 'knife'; SELECT docid, body FROM review_search WHERE review_search MATCH 'kni*';

▶ RunRestore the queryFresh database

A prefix is crude: kni* would also find "knitted". Real search engines have analysers for each language that reduce words to their stem (stemming): "tomatoes" becomes "tomato". SQLite has one only for English (tokenize=porter), and Elasticsearch and OpenSearch have them for many languages. Irregular forms like "knives" trip up even those.

Try it

Tomatoes

Find all the reviews about tomatoes, whatever the form of the word, in the index (columns docid and body).

SQLdatabase: shop

SELECT docid, body FROM review_search LIMIT 3;

SELECT docid, body FROM review_search LIMIT 3;

Check▶ Run

The idea

More than one word

Two words one after the other mean AND: the review must have both. OR means at least one of them:

SQLdatabase: shop

-- Both words (AND is implied)
SELECT docid, body FROM review_search WHERE review_search MATCH 'coffee delivery';
-- One or the other
SELECT docid, body FROM review_search WHERE review_search MATCH 'apples OR tomatoes';

-- Both words (AND is implied) SELECT docid, body FROM review_search WHERE review_search MATCH 'coffee delivery'; -- One or the other SELECT docid, body FROM review_search WHERE review_search MATCH 'apples OR tomatoes';

▶ RunRestore the queryFresh database

A phrase in quotes looks for the words next to each other and in that order. NEAR/3 looks for the two words at most 3 words apart:

SQLdatabase: shop

-- Exactly this phrase, the words next to each other
SELECT docid, body FROM review_search WHERE review_search MATCH '"like new"';
-- The two words at most 3 words apart
SELECT docid, body FROM review_search WHERE review_search MATCH 'pan NEAR/3 good';

-- Exactly this phrase, the words next to each other SELECT docid, body FROM review_search WHERE review_search MATCH '"like new"'; -- The two words at most 3 words apart SELECT docid, body FROM review_search WHERE review_search MATCH 'pan NEAR/3 good';

▶ RunRestore the queryFresh database

NOT removes the documents with a given word: coffee NOT date.

More precisely, if you are curious

FTS4 has two query syntaxes. In the standard one "without the word" is written with a minus (coffee -date), and in the enhanced one with NOT. sql.js, which runs SQLite in the browser, uses the enhanced one, and there -date is simply the word "date". The enhanced one also allows brackets: (apples OR tomatoes) NOT torn. In Elasticsearch the same is a bool query with must, should and must_not.

Try it

Coffee without complaints

Find the reviews about coffee that do not mention a date (columns docid and body).

SQLdatabase: shop

SELECT docid, body FROM review_search WHERE review_search MATCH 'coffee';

SELECT docid, body FROM review_search WHERE review_search MATCH 'coffee';

Check▶ Run

The idea

Snippets and order

A search engine shows a snippet with the word marked. snippet does it on its own:

SQLdatabase: shop

SELECT docid, snippet(review_search, '[', ']', '…') AS found
FROM review_search
WHERE review_search MATCH 'bread';

SELECT docid, snippet(review_search, '[', ']', '…') AS found FROM review_search WHERE review_search MATCH 'bread';

▶ RunRestore the queryFresh database

The first results should be the most relevant. Real search engines order them by BM25: the rarer the word across all the documents and the more often it is in this one, the higher, and a short text with the word beats a long one. The table fts4aux from the card on the index gives the numbers for that: how many documents each word is in. FTS4 does not order on its own (FTS5 and Elasticsearch have BM25 built in), so here the order comes from ORDER BY.

Exercise

Searching with the products

1. The index keeps only the text. The name of the product and the rating are in the tables.

Find the reviews about coffee with the name of the product and the rating (columns name, rating and body), from the highest rating down.

SQLdatabase: shop

SELECT docid FROM review_search WHERE review_search MATCH 'coffee';

SELECT docid FROM review_search WHERE review_search MATCH 'coffee';

Check▶ Run

2. The index is a separate table and does not know on its own when reviews changes. A trigger updates it on every new review:

SQLdatabase: shop

CREATE TRIGGER review_added AFTER INSERT ON reviews
BEGIN
  INSERT INTO review_search (docid, body) VALUES (new.id, new.body);
END;

INSERT INTO reviews (product_id, customer_id, rating, body, created_at)
VALUES (17, 4, 5, 'The coffee arrived freshly roasted.', '2026-10-25');

SELECT docid, body FROM review_search WHERE review_search MATCH 'coffee';

CREATE TRIGGER review_added AFTER INSERT ON reviews BEGIN INSERT INTO review_search (docid, body) VALUES (new.id, new.body); END; INSERT INTO reviews (product_id, customer_id, rating, body, created_at) VALUES (17, 4, 5, 'The coffee arrived freshly roasted.', '2026-10-25'); SELECT docid, body FROM review_search WHERE review_search MATCH 'coffee';

▶ RunRestore the queryFresh database

Write a trigger review_removed that removes the review from review_search when it is deleted from reviews.

SQLdatabase: shop

SELECT COUNT(*) FROM review_search;

SELECT COUNT(*) FROM review_search;

Check▶ Run

The idea

When a separate search engine

For searching a few thousand or million texts the full-text index of the database is enough: FTS in SQLite, tsvector and a GIN index in PostgreSQL, FULLTEXT in MySQL. A separate search engine (Elasticsearch, OpenSearch, Meilisearch, Typesense) pays off when search is the main job of the site: analysers for many languages, fixing typos ("cofee" → "coffee"), filters by category with counts next to them, suggestions while you type, and many machines. Then the data is copied into it, usually from an event stream, and the truth stays in the database.

More precisely, if you are curious

In PostgreSQL the same search is WHERE to_tsvector('english', body) @@ to_tsquery('english', 'coffee') with the index CREATE INDEX ON reviews USING gin (to_tsvector('english', body)). In Elasticsearch the field gets "analyzer": "english" and the search is {"query": {"match": {"body": "coffee"}}}.

Test

Check yourself

1. Why is LIKE '%coffee%' slow with a million reviews?

The % at the start rules out an index, and the database reads every text from start to endBecause LIKE is an old commandBecause coffee is popular

2. In the full-text index MATCH 'knife' does not find "The knives cut perfectly". Why?

The review is bad"knives" is a different word in the index, not "knife"The capital T

3. What does an inverted index do?

Stores the texts backwardsFor every word it keeps which documents (and where in them) it appears inSorts the reviews by rating

4. The reviews are kept in reviews, and the search in review_search. What happens if someone deletes a review and there is no trigger?

Nothing, the index notices on its ownThe search still finds the deleted reviewThe table reviews comes back

Check

Start the lesson over

Related lessons

• Builds on: INSERT: new rows · DELETE: deleting rows · Triggers · Indexes
• Continues in: Searching by meaning · How to choose a database
• Tasks on this topic: Kinds of databases