MYSQL vs. match did not show the same results as mysql, e.g. %% query

Question

We just transfer all mysql queries as %% to MATCH Against

Our old waS request

SELECT * from jobs where jobtitle like '%php%' 

I tried the following queries

 SELECT * from jobs where MATCH(jobtitle) AGAINST ('php') SELECT * from jobs where MATCH(jobtitle) AGAINST ('php' IN BOOLEAN MODE) 

I tried * + and all but one query show the same exact results that look like "% php%"

I need a mysql query using match to to find the word anywhere in the job title

Answer 1

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

You need to change its value and rebuild your FULLTEXT indices. If you want the three-digit 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 superuser privileges, you can set this environment variable with:

 SET ft_min_word_len=3 

Check for more details: http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html

Answer 2

In mysql v5.6, the default value of the ft_min_word_len server variable is 4, which means that the search string "php" will not return any lines in natural language mode and logical mode, unless you reduce the limit to 3 and recreate full-text indexes.

In natural language mode on myisam tables, the query will not return any rows if the word php is present in more than 50% of the rows. So use logical mode with the + operator.