MySQL Full-Text search Multi-column FULLTEXT search
Example
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 ISBN, Title, and 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);
