MySQL Full Text Search for Words with Three or Less Letters

Fulltext search on mysql with a 3 letter word

See this answer MySQL full text search for words with three or less letters

[mysqld]

ft_min_word_len=3

Then you must restart the server and rebuild your FULLTEXT indexes.

Remeber to restart and rebuild indexes.

Edit

Run show variables like 'ft_%'; to confirm that the word length matches what you set it to.

how do I fix full text search for 3 characters?

Please see Fine-Tuning MySQL Full-Text Search. You first need to change
ft_min_word_len (or innodb_ft_min_token_size for InnoDB tables) and then rebuild the fulltext search indexes.

MySql Fulltext Search not correct with short words

Question

This has been discussed on SO quite a few times: MySQL's built-in fulltext parser is designed for searching for words, not for single characters and comes with default minimum word length setting of 3 (innodb) or 4 (myisam) These settings mean that no words shorter than 3 or 4 words get indexed and therefore will not be found by a fulltext search. You may lower the minimum character length limit to 1 and rebuild the index, but it will slow the searching down, since the indexes will be bigger.

Question

It is possible, but you need to search on the title field separately and bump up the relevancy score results from the title field.

You can use union to get a combined list with sum() to sum the score up for any record:

SELECT p.id, any_value(title), any_value(description), any_value(tags), sum(t.score) as sum_score
FROM
    (SELECT id, (MATCH(title) AGAINST ('$search' IN NATURAL LANGUAGE MODE)) *2 AS score
     FROM pages
     UNION ALL
     SELECT id, MATCH(description,tags) AGAINST ('$search' IN NATURAL LANGUAGE MODE) AS score
     FROM pages) t
INNER JOIN pages p on t.id=p.id
GROUP BY p.id
ORDER BY sum(t.score) DESC

You need to adjust the fulltext indexes to be able to do the separate searches.

mysql full-text search not working for 3 characters

The minimum and maximum lengths of words to be indexed are defined by the ft_min_word_len and ft_max_word_len system variables. The default minimum value is 4 characters. That is why it's not working with 3 characters.

You need to change its value, and rebuild your FULLTEXT indexes. If you want three-character words to be searchable, you can set the ft_min_word_len variable by changing its value in the configuration file. Or, if you have super user permissions you can set this environment variable with:

  SET  ft_min_word_len=3

Get more details here: Fine-Tuning MySQL Full-Text Search

MySql Fulltext search using 2 character word

I found that this issue was specific to innodb storage engine. I've solved the issue by adding
[mysqld] innodb_ft_min_token_size =2; to /etc/my.cnf