A full-text index is a specialized database index for searching within large text values. It makes keyword searches practical instead of requiring a slow linear scan of every text value. section 4.5 section 7.9
Use a full-text index when the requirement is keyword search rather than exact matching:
- Identify a text-search interaction, such as users searching for products by name. section 7.10
- Create the full-text index on the searched column. section 7.10
- Search with MATCH and AGAINST in MySQL or MariaDB. section 7.10
- Use a standard index for exact matches, such as a product code or email address. section 7.10
Searching product names
For a product table, create a full-text index on name, then search for matching words such as “some text.” A LIKE search over a large text column can be replaced by a full-text search. section 7.10 section 7.9
-- comment: works for MySQL and MariaDB -- comment: consult ChatGPT on adaptations -- comment: for other RDBMS CREATE FULLTEXT INDEX ft_idx_name ON product (name);
Shows the SQL statement for creating a full-text index on product.name. section 7.10
-- comment: works with MySQL and MariaDB
-- comment: consult ChatGPT on adaptations
-- comment: for other RDBMS
SELECT * FROM product WHERE MATCH (name)
AGAINST ('some text' IN NATURAL LANGUAGE MODE);
Shows the corresponding MATCH ... AGAINST keyword query in natural-language mode. section 7.10
