SQL
Implementing Full-Text Search in SQL
Discover how to implement efficient and relevant full-text search capabilities directly within your SQL database using built-in features, improving search results.
-- PostgreSQL example (using tsvector and tsquery)
-- First, create an index for performance
-- CREATE INDEX articles_search_idx ON articles USING GIN(to_tsvector('english', title || ' ' || body));
SELECT
id,
title,
ts_rank(to_tsvector('english', title || ' ' || body), query) AS rank
FROM
articles,
to_tsquery('english', 'web & development') AS query
WHERE
to_tsvector('english', title || ' ' || body) @@ query
ORDER BY
rank DESC;
-- MySQL example (using FULLTEXT index)
-- First, create a FULLTEXT index on your columns
-- ALTER TABLE products ADD FULLTEXT(name, description);
SELECT
id,
name,
MATCH(name, description) AGAINST('red shirt' IN NATURAL LANGUAGE MODE) AS score
FROM
products
WHERE
MATCH(name, description) AGAINST('red shirt' IN NATURAL LANGUAGE MODE);
How it works: This snippet demonstrates how to implement full-text search directly within your database. PostgreSQL uses `to_tsvector` and `to_tsquery` operators, often with a GIN index, for highly configurable and efficient search. MySQL uses the `MATCH AGAINST` clause with `FULLTEXT` indexes. This approach provides more accurate and faster search results compared to simple `LIKE` queries, especially for large text fields.