Bug: Searching entries: Can't find full words

[jkorff]

Description

This is a weird behaviour that I noticed now that I've loaded Craft with a lot of entries:
When I type words to search entries for their titles Craft doesn't find them although they're there!

It cannot find entries whose titles contain words such as "little", "various", "always" or "more".
But when I reduce the number of letters to say "lit" or "var" the entries show up.

Screenshot: Searching for "little":

Screenshot: Searching for "lit" brings up many more entries with the word "little" in their title:

My search index is current and fresh.

Steps to reproduce

1. Craft > Entries
2. Start typing one of the words

Additional info

• Craft version: 3.0.12
• PHP version:
• Database driver & version:
• Plugins & versions:

[billythekid]

Noticed a similar bug possibly related to this, unsure if I should open a new report here @brandonkelly?

If you have full stops(periods) in your search term it doesn't find the results. For example I have a commerce product called "Omega Seamaster Aqua Terra 220.10.34.20.53.001" and if I type 220 it's found but if I type 220.10.34.20.53.001 or even just 220.10 there are no results.

[angrybrad]

@jkorff "little", "various", "always" and "more" are all in MySQL's MyISAM full text default stop word list, which means MySQL will ignore those in searches by default. See bottom half of page here: https://dev.mysql.com/doc/refman/8.0/en/fulltext-stopwords.html

You'd need to edit that list on your server if you want them to get picked up.

The reason "lit" will match is because (by default), MyISAM full text search also has a minimum word length of 4. If Craft sees a word that is less than that, it will not do a full text search but a direct SQL LIKE query instead and return the results from it.

@billythekid Craft strips all punctuation from search terms before storing them in its search index.

[jkorff]

How do I explain this to my users?
Does this mean that Craft will behave like this by design?

I'd rather have a config option where I can turn this behaviour off.

[angrybrad]

Does this mean that Craft will behave like this by design?

MySQL behaves like that by design. Craft/PHP can't modify those types of MySQL settings.

If you can't modify your MySQL server settings, then a stop-gap if you're running MySQL 5.6+ is to convert that full-text index on the craft_search table from MyISAM to InnoDB. That's when InnoDB added support for full text searching and it's stop word list is much smaller (36 from the previous link).

[jkorff]

Thank you for pointing out that option.

There is no way Craft can set a dynamic MySQL variable? The MyISAM equivalent to
mysql_query("SET innodb_ft_enable_stopword = false;");

[jkorff]

Stop words are driving me nuts. I have to learn to avoid stop words and use partial words to find entries with lots of stop words in their titles.

I've had at least one article duplicated because an editor could not find the existing article for this reason.

Please, can you add a config option to disable stop words?
Edit: I am after a clean solution, not the table hack suggested earlier.

[brandonkelly]

That wasn’t a hack. If you are running MySQL 5.6+, using a InnoDB fulltext index on the search.keywords column is preferable.

[jkorff]

Ok, so it's a configuration option within the database system. Wouldn't it be better placed though with all the other config options in general.php?

It's like when I'm driving my car: I can "configure" my speed anytime (general.php), but when I want to change my oil settings (stop words) I need to stop driving and most likely seek a mechanic to change the fill level (change db table type).

I'm also concerned that not all developer-client scenarios have that full access to the database tables.

[brandonkelly]

Wouldn't it be better placed though with all the other config options in general.php?

That would be ideal, sure, but Craft can’t control everything. Going with a car analogy, this is like telling the auto manufacturer that it would be nice if the car had a button to change the weather.

[jkorff]

Hm, I think it's more like a 4WD mode: Not all users need it, it might be less fuel efficient, but it takes you places that are otherwise hard to get to. (And given the reference to issue #3406 it appears there are a few more drivers who think so.)

[missmatsuko]

Since 3.1.12, searching with an InnoDB craft_searchindex table causes errors:
#3862

[brandonkelly]

For posterity, #3862 is now resolved.