SQL
Implementing Basic Full-Text Search with MATCH AGAINST
Learn to perform keyword-based searches across multiple text columns in MySQL using the `MATCH AGAINST` clause for efficient full-text querying.
-- First, ensure your table has a FULLTEXT index on the relevant columns:
-- ALTER TABLE products ADD FULLTEXT(name, description);
SELECT
id,
name,
description,
MATCH(name, description) AGAINST ('search term' IN BOOLEAN MODE) AS relevance_score
FROM products
WHERE
MATCH(name, description) AGAINST ('search term' IN BOOLEAN MODE)
ORDER BY
relevance_score DESC;
-- Example with multiple terms and operators:
-- SELECT id, name FROM articles
-- WHERE MATCH(title, body) AGAINST ('+database -sql' IN BOOLEAN MODE);
-- This finds articles containing "database" but not "sql".
How it works: This SQL snippet demonstrates how to implement basic full-text search capabilities using MySQL's `MATCH AGAINST` clause. It requires a `FULLTEXT` index on the columns you wish to search (`name`, `description`). The query searches for 'search term' within these indexed columns. `IN BOOLEAN MODE` allows for powerful operators (like `+` for required words, `-` for excluded words). The `relevance_score` helps order the results by how well they match the search query.