Full-Text Search

LIKE '%keyword%' is slow on large tables and doesn't understand words: it can't rank results by relevance, match "running" when searching for "run", or find all of several words in any order. Full-text search does all of that, using a special index of words.

SQLite: FTS5

-- standalone
CREATE VIRTUAL TABLE articles USING fts5(title, body);

INSERT INTO articles (title, body) VALUES
  ('Getting started with SQL', 'Learn how to query tables with SELECT and filter rows with WHERE.'),
  ('Indexing for speed', 'Indexes make queries faster by avoiding full table scans.'),
  ('Window functions', 'Rank rows and compute running totals with window functions over partitions.');

SELECT title FROM articles WHERE articles MATCH 'queries' ORDER BY rank;
SELECT title FROM articles WHERE articles MATCH 'rows AND running' ORDER BY rank;
SELECT title, snippet(articles, 1, '[', ']', '...', 6) AS excerpt
FROM articles WHERE articles MATCH 'table*' ORDER BY rank;

+--------------------+
|       title        |
+--------------------+
| Indexing for speed |
+--------------------+
+------------------+
|      title       |
+------------------+
| Window functions |
+------------------+
+--------------------------+-------------------------------------------+
|          title           |                  excerpt                  |
+--------------------------+-------------------------------------------+
| Indexing for speed       | ...faster by avoiding full [table] scans. |
| Getting started with SQL | Learn how to query [tables] with...       |
+--------------------------+-------------------------------------------+

• MATCH searches the full-text index; ORDER BY rank puts the most relevant results first.
• Queries support AND, OR, NOT, phrases in quotes, and prefix searches (table* matches "table" and "tables").
• snippet() returns an excerpt with the matching words highlighted.

Adding tokenize = 'porter' to the table options enables stemming, so "query" also matches "queries" and "querying".

PostgreSQL

-- PostgreSQL
ALTER TABLE articles ADD COLUMN search tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

CREATE INDEX idx_articles_search ON articles USING GIN (search);

SELECT title, ts_rank(search, query) AS rank
FROM articles, websearch_to_tsquery('english', 'running totals') AS query
WHERE search @@ query
ORDER BY rank DESC;

to_tsvector breaks text into normalized words (stemming, removing common words like "the"), and websearch_to_tsquery accepts search-engine style input. MySQL offers FULLTEXT indexes with MATCH ... AGAINST.

When you need more

For typo tolerance, faceted filtering, and search across millions of documents, dedicated search engines (Elasticsearch, OpenSearch, Meilisearch, Typesense) are common — usually kept in sync with the main database. For many applications, the database's built-in full-text search is enough.