A keyword query finds movie records containing specified words or terms. For title searches that need partial-word or typo-tolerant matching, use a full-text index rather than relying only on a standard index. section 7.9
Use keyword searching when the application needs to:
- Find titles containing a word or term. section 7.9
- Support partial-word matching or fuzzy searching for misspellings. section 7.9
- Keep text searches efficient as the data grows. section 7.9
Searching movie titles
A basic search uses `WHERE title LIKE '%exciting%'`. A full-text search adds an index and uses `MATCH(title) AGAINST('exciting' IN NATURAL LANGUAGE MODE)`, making the same task more efficient. section 7.9
-- comment: works for MySQL and MariaDB
-- comment: consult ChatGPT on adaptations
-- comment: for other RDBMS
-- comment: add a full-text index
CREATE FULLTEXT INDEX ft_idx_title
ON movie (title);
-- comment: search
SELECT *
FROM movie
WHERE MATCH(title)
AGAINST('exciting' IN NATURAL LANGUAGE MODE);
Inspect the MySQL and MariaDB full-text index and search commands for the movie title example. section 7.9
