Using MySQL full text queries, in my experience, are not as fast as using Solr/Elastic Search, but do not require the additional provisioning and maintence of a search server.
You need to add a FULL TEXT index on the columns you wish to query.
The questions marks are to be replaced by the query, for example +mental +health
.
SELECT *, MATCH (name,summary) AGAINST (? IN BOOLEAN MODE) as score FROM documents WHERE MATCH (name,summary) AGAINST (? IN BOOLEAN MODE) > 0 ORDER BY score DESC;
The MATCH
is required twice in order to exclude results with a score of 0
.
SELECT *, MATCH (name,summary) AGAINST (? IN BOOLEAN MODE) as score FROM documents WHERE MATCH (name,summary) AGAINST (? IN BOOLEAN MODE) > 0 AND type IN('document', 'event') ORDER BY score DESC;