Mysql Fulltext search doesn't return any results

Question

I have an issue with a full text search in a MySQL table.

MATCH AGAINST returns no results even I if have 7 records containing the words I'm looking for. What can I do to make it return the rows?

SELECT * 
FROM site_plugin_products_cache_texts 
WHERE MATCH(item_text) AGAINST ('+your +name' IN BOOLEAN MODE);

No rows.

SELECT * 
FROM site_plugin_products_cache_texts 
WHERE item_text LIKE'%your name%'

7 rows (0.071 s)

http://sqlfiddle.com/#!9/70c3fa/1/0

Thanks.

Answer 1

Did you know that MySQL has a stop word list ?

For MyISAM, there are 543 words that do not get included in a fulltext index ???

In the MySQL 5.6 Docs on this (Stopwords for MyISAM Search Indexes), you can see the list.

• name is on line 55 column 4
• your is on line 108 column 4

That why all attempts to search on those two words will fail.

If you convert the table to InnoDB, drop and create the FullText index, you will find them. There are only 36 stop words for InnoDB FullText Indexes.

If the table has to remain MyISAM, you have to provide an empty stopword list, restart mysqld, drop and create the FullText Index again.

Here are some of my past posts on this:

• May 23, 2011 : Optimizing mysql fulltext search (StackOverflow)
• Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
• Jan 26, 2012 : Mysql fulltext search my.cnf optimization
• May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
• Apr 24, 2013 : MySQL - SQL statement to test minimum length of the word 'ft_min_word_len'

Please refer to the Jan 26, 2012 post on how to setup and empty stopword list.

UPDATE 2018-05-04 22:44 EDT

If you run this in SQL Fiddle

SELECT * FROM site_plugin_products_cache_texts
WHERE match(item_text) AGAINST ('+CALL' IN BOOLEAN MODE);

This will work because CALL in not in the stopword list.