MySQL Fulltext Index Searching

MySQL Fulltext Index Searching

So I recently want to create a search using Full Text Index searching but wasn’t sure how to go about it, I had to lookup multiple resources and still had hiccups along that way so figured I’d create a quick look up for myself (and others) for the future, so here goes:

Enabling MySQL FullText Indexing on a Database

The first thing you have to do before you can do a FullText search on a table is enable FullText Indexing, the easiest way to do this is by running this simply query against the database:

ALTER TABLE table_name ADD FULLTEXT (columnName1, columnName2);
Careful! The order and exact number of columns you select is important and needs to be noted for later use (the ‘MATCH…AGAINST’ function must have the same columns in the same order or the search will fail).

Performing a FullText Search in MySQL

SELECT * FROM table_name WHERE MATCH (columnName1, columnName2) AGAINST ('banana') AS relevance;

No Results When Performing MySQL FullText Search

Sometimes even when everything is correct, your search will return no results – this can be due to a number of factors but the most common are:

No Results Returned for Short Searches

MySQL has a pre-set minimum string length for fulltext searches of 4 characters so any search term under that (for example ‘PHP’) is disregarded – you can change this by editing the following option:

ft_min_word_len = 4

in the appropriate configuration file (which varies depending on your MySQL Version) to whatever length you prefer.  For example, to allow searching for 3 character terms, change it to:

ft_min_word_len = 3

You will then need to restart MySQL and also rebuild any FullText indexes on tables created before the change using:

repair table table_name quick;

Too Many Results

If one of your search terms matches more than 50% of the rows in a table, MySQL will consider it a ‘noise word’ and disregard it.  You *can* get around this by making the search BOOLEAN MODE where it searches for both terms but note that this means that the returned relevance is always 1.

SELECT * FROM table_name WHERE MATCH (columnName1, columnName2) AGAINST ('banana' IN BOOLEAN MODE) AS relevance;
Bob McKay

About Bob McKay

Bob McKay works at Perfect Image, is a father, programmer and a self confessed techie-geek type.

Disclosure Policy

Bob on Google+

Leave a Reply

Your email address will not be published. Required fields are marked *