SET @searchTerm= 'Date Database Programming'; SELECT MATCH (Title, Author) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE) Score, ISBN, Author, Title FROM book WHERE MATCH (Title, Author) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE) ORDER BY MATCH (Title, Author) AGAINST (@searchTerm IN NATURAL LANGUAGE MODE) DESC;
Given a table named book with columns named
Author, this finds books matching the terms 'Date Database Programming'. It shows the best matches first. The best matches include books written by Prof. C. J. Date.
(But, one of the best matches is also The Date Doctor's Guide to Dating : How to Get from First Date to Perfect Mate. This shows up a limitation of FULLTEXT search: it doesn't pretend to understand such things as parts of speech or the meaning of the indexed words.)
For this to work, a fulltext index on the Title and Author columns must be available:
ALTER TABLE book ADD FULLTEXT INDEX Fulltext_title_author_index (Title, Author);