Full-text search

Oracle

-- Oracle Text: create a CONTEXT index
CREATE INDEX article_idx ON article(content) INDEXTYPE IS CTXSYS.CONTEXT;
-- Search with CONTAINS (label 1 ties CONTAINS to SCORE):
SELECT id, SCORE(1) FROM article
WHERE CONTAINS(content, 'database AND performance', 1) > 0
ORDER BY SCORE(1) DESC;

Oracle Text (formerly interMedia) provides language-aware full-text search through a CONTEXT index. The CONTAINS operator supports search operators such as AND/OR/NEAR. The index is maintained with CTX_DDL.SYNC_INDEX or automatic SYNC.

PostgreSQL

-- Search with tsvector/tsquery, accelerated by a GIN index:
CREATE INDEX article_fts_idx ON article
  USING GIN (to_tsvector('english', content));
SELECT id, ts_rank(to_tsvector('english', content), q) AS score
FROM article, to_tsquery('english', 'database & performance') q
WHERE to_tsvector('english', content) @@ q
ORDER BY score DESC;

PostgreSQL built-in full-text search matches tsvector (document) against tsquery (query) with the @@ operator. A GIN index speeds up searches over large text. The language configuration ('english') performs stemming and stop-word handling.

MySQL

-- Create a FULLTEXT index (InnoDB/MyISAM):
ALTER TABLE article ADD FULLTEXT INDEX ft_content (content);
-- Search in natural language / boolean mode:
SELECT id, MATCH(content) AGAINST('database performance') AS score
FROM article
WHERE MATCH(content) AGAINST('+database +performance' IN BOOLEAN MODE);

A MySQL FULLTEXT index is queried with the MATCH ... AGAINST syntax. IN NATURAL LANGUAGE MODE returns a relevance score; IN BOOLEAN MODE gives fine-grained control with the +/-/* operators. InnoDB supports full-text search from 5.6 onward.

SQL Server

-- Create a Full-Text Catalog and index:
CREATE FULLTEXT CATALOG ft_catalog;
CREATE FULLTEXT INDEX ON dbo.article(content)
  KEY INDEX PK_article ON ft_catalog;
-- Search with CONTAINS / FREETEXT:
SELECT id FROM dbo.article
WHERE CONTAINS(content, '"database" AND "performance"');

SQL Server Full-Text Search first needs a Full-Text Catalog, then a Full-Text Index on the table (a unique KEY INDEX is mandatory). CONTAINS supports exact/proximity search and logical operators; FREETEXT supports meaning-based (linguistic) search. The Full-Text Search component must be installed.

MongoDB

// Create a text index:
db.article.createIndex({ content: "text" })
// Search with $text and sort by relevance score:
db.article.find(
  { $text: { $search: "database performance" } },
  { score: { $meta: "textScore" } }
).sort({ score: { $meta: "textScore" } })

A MongoDB text index provides full-text search through the $text operator; a collection can have only one text index. The textScore meta field gives the relevance. For more advanced search, Atlas Search ($search, Lucene-based) is used.

Redis

# Requires the RediSearch module (Redis Stack).
# Define the index:
FT.CREATE article_idx ON HASH PREFIX 1 article: SCHEMA content TEXT
# Full-text search:
FT.SEARCH article_idx "database performance"

Core Redis does not offer full-text search; the RediSearch module (shipped with Redis Stack) provides indexing with FT.CREATE and searching with FT.SEARCH. Stemming, fuzzy search, filtering and sorting are supported. Without the module installed, the feature is unavailable.

Elasticsearch

# Elasticsearch is natively a full-text search engine.
# match query (analyzed text search):
GET article/_search
{
  "query": {
    "match": { "content": "database performance" }
  }
}

Elasticsearch is fundamentally a full-text search engine built on Lucene; text fields are analyzed at index time (tokenization, stemming). The match query returns results sorted by relevance score (_score). Language-specific analyzers (e.g. 'english') can be assigned.