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);
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;