This tutorial uses MySQL version 5.6.
For this tutorial, a simple table is created as follows
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
title VARCHAR(25) NOT NULL COMMENT 'The name of the tutorial',
body TEXT NOT NULL COMMENT 'The body of the tutorial'
Prior to MySQL version 5.6, FULL TEXT searches could only be performed on MyISAM tables, however, with the advent of 5.6 and creater, InnoDB tables can also be searched.
In MySQL 5.6, FULL TEXT seaches may be performed on CHAR, VARCHAR, and TEXT fields.
Previously, searches in MySQL were performed using the LIKE operator. Queries like this:
Whilst this method of searching records works, several problems arise. If the keyword is too generic, far too many results are returned. There is also no way of determining th e relevency of the returned results, and perhaps the greatest problem, is that the query is slow as it uses string comparisons on every record title and body. As the database grows, the query will become slower and slower.
To alleviate this, MySQL introduced FULL TEXT searching.
Our simple table above has a unique identifier with the primary key index. For FULL TEXT searching, a FULL TEXT INDEX needs to be applied to the fields which needs to searched.
We begin the FULL TEXT search by adding FULL TEXT indexes to the fields that will be searched. In this case, the title and body fields.
With the FULLTEXT index in place, a query can now be created to search.
Now the search will match the title and body fields against the text 'keyword'.
The above query correct retrieves the required results, however, in most instances, the top scoring results are required.
id, title, body, MATCH(title, body)
AGAINST ('keyword') `AS score
WHERE MATCH(title, body) AGAINST('keyword')
ORDER BY score DESC;
The search now scores each result, and returns the score ORDERed by relevancy. Enjoy