0

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.

2

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:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.