How-to: Full text search in MySQL

MySQL has had FULLTEXT searching in one form or another since version 3.23.23. FULLTEXT indices in MySQL allow database administrators and programmers to designate any character-based field (CHAR, VARCHAR, or TEXT) as a FULLTEXT index, which allows for complex text searching against data stored in those fields.

This feature is not to be confused with the LIKE function in MySQL. LIKE works more along the lines of a regular expression. On the other hand, FULLTEXT indices are fully indexed fields which support stopwords, boolean searches, and relevancy ratings.

How does it work ?

Adding Full Text to your table is very easy it will remind you of adding regular keys or indexes. We will begin with creating a simple news table and fill it with some content:

CREATE TABLE news
(
ID INT(9) UNSIGNED NOT NULL DEFAULT '0' AUTO_INCREMENT,
title CHAR(255) NOT NULL DEFAULT '',
body TEXT NOT NULL DEFAULT '',
PRIMARY KEY (ID),
FULLTEXT(title,body)
);

INSERT INTO news
VALUES (1,
'Pellentesque mauris',
'Pellentesque mauris turpis, porta vel, euismod sed,
pulvinar vitae, tortor. Duis accumsan. Suspendisse facilisis blandit mauris.');

INSERT INTO news
VALUES (2,
'Mauris eu massa',
'Praesent varius orci sit amet erat. Suspendisse sed quam non sapien
consectetuer scelerisque. Aliquam mauris pede, vestibulum vitae,
dapibus vel, cursus at, sapien. Nullam vel purus sit amet quam placerat
suscipit.');

INSERT INTO news
VALUES (3,
'Cum sociis',
'Ut auctor eleifend magna. Morbi porttitor, felis nec accumsan
elementum, lectus justo interdum sapien, non volutpat felis est quis
felis. Vestibulum nulla.');

INSERT INTO news
VALUES (4,
'Quisque dolor!',
'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Sed sapien
nunc, feugiat et, ultrices eget, aliquam non, nulla. Cras mollis. Duis
eget eros. In mauris. Mauris purus massa, mollis at, malesuada in,
vulputate vitae, odio. Duis ut dolor. Phasellus id nunc non est dictum
porttitor.');

Getting results

Now that we have filled our table with some news we can start doing some queries.

SELECT ID,title
FROM news
WHERE MATCH (title,body) AGAINST('sociis');

Ok this is simplest full text search query. There are few things you must know tho.
Columns in match() must always be the same ones that we defined as fulltext.

With fulltext you will get results sorted by their relevance aka score, lets display that score.

SELECT ID,title
FROM news
WHERE MATCH (title,body) AGAINST('sociis') as score;

Using full text search doesn’t not forbid you to use other sql statements, for example limit etc.

Boolean mode

SELECT ID,title
FROM news
WHERE MATCH (title,body) AGAINST('+sed -mauris') as score;

You can filter results given by searching with boolean query. Look at against(), you will notice + and – signs in front of keywords, + sign means that word will be included in search and minus sign means that all results containing this keyword will be excluded from search.

And at the end I must warn you that Full Text search is not supported on InnoDB table engine. (so referential integiry and full text don’t stack)