hello world hello world - 6 months ago 47
MySQL Question

Prevent MySQL fulltext natural language search from ignoring words which appear in at least 50% of rows

When searching MyISAM tables, MySQL fulltext search in natural language mode ignores words which appear in at least 50% of rows. Is there any way to prevent this behavior? I would like to include popular words rather than ignore them.

For example, I have a MySQL table of clothing entries, 90% of which are categorized as t-shirts. I would like customers to be able to search for "t-shirt" without having the "t-shirt" word ignored in the fulltext index.

The only solution I have come up with so far is to use boolean mode search rather than natural language search. However, the scores that boolean mode search returns don't seem to be as diverse or meaningful as scores returned by natural language search.

Side Note: As of MySQL 5.6 and up, natural language search on InnoDB tables does not ignore words which appear in at least 50% of rows. However, on my web server, I am still using MySQL 5.5 which doesn't offer fulltext search on InnoDB tables, so the table needs to remain in MyISAM format.

Thanks!

Answer

A couple years later, I have found the answer in documentation for MySQL 5.7:

For MyISAM search indexes, the 50% threshold for natural language searches is determined by the particular weighting scheme chosen. To disable it, look for the following line in storage/myisam/ftdefs.h:

#define GWS_IN_USE GWS_PROB

Change that line to this:

#define GWS_IN_USE GWS_FREQ

Then recompile MySQL. There is no need to rebuild the indexes in this case.