PHP How To Sort MYSQL Fulltext Search By Relevancy

By Kevin Waterson 21209 12 Comments

MySQL
The Old Way
FULL TEXT
Relevancy

MySQL

This tutorial assumes a basic understanding of PHP and MySQL. Please read the MySQL tutorial.

This tutorial uses MySQL version 5.6.

The Old Way

For this tutorial, a simple table is created as follows

CREATE TABLE tutorial(
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:

SELECT id, title, body FROM tutorials WHERE title LIKE '%keyword%' OR body LIKE '%keyword%';

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.

FULL TEXT

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.

ALTER TABLE tutorials ADD FULLTEXT( title, body );

With the FULLTEXT index in place, a query can now be created to search.

SELECT id, title, body FROM tutorials WHERE MATCH(title, body) AGAINST ('keyword')

Now the search will match the title and body fields against the text 'keyword'.

Relevancy

The above query correct retrieves the required results, however, in most instances, the top scoring results are required.

SELECT
id, title, body, MATCH(title, body)
AGAINST ('keyword') `AS score
FROM tutorials
WHERE MATCH(title, body) AGAINST('keyword')
ORDER BY score DESC;

The search now scores each result, and returns the score ORDERed by relevancy. Enjoy