Mroonga and Pgroonga search index support for CJK languages

[ishitatsuyuki]

Hi,

We're using Craft CMS to build a website in Japanese, and we have a little trouble with the behavior of the search.

English-centric search indexers like MyISAM have trouble tokenizing CJK languages as the word boundaries are not explicitly denoted by space like in English-like languages. As such, it can omit results in unexpected ways or otherwise result in poor search results.

The Mroonga extension is a drop-in replacement to MyISAM that provides CJK language indexing. Using it is easy: Just ALTER TABLE set the engine and it's done. The Pgroonga does the same, but for Postgres.

I would like to see official support for this in Craft, mostly so that the schema doesn't get broken accidentally. It would also be good to see feature detection (checking if extension is loaded) and one-click switching — but I know that this is a niche use case so that isn't 100% necessary.

[ishitatsuyuki]

Perhaps a good approach would be to include the Mroonga index as a separate table — this way it can go through schema changes independently.

[brandonkelly]

We are dropping MyISAM as the default searchindex table engine in Craft 4.1 in favor of InnoDB (like all other tables) – see #11374.

Any idea whether InnoDB does better with CJK languages?

[ishitatsuyuki]

In later versions of MySQL, using the ngram tokenizer (or mecab tokenizer for even better semantic parsing) should provide a passable performance and fixing issues like blatantly omitting rows that contains the search string.

This does require a schema alteration, see below.

https://dev.mysql.com/doc/refman/8.0/en/fulltext-search-ngram.html

[brandonkelly]

Would making this change to the searchindex table have a negative effect on non-CJK languages? If not, I’d recommend you just go in and make the changes yourself.

Manually modifying the searchindex table schema is already commonplace, or at least it will be until 4.1 ships and starts using InnoDB for that table by default.

We can’t really use feature detection when it comes to defining the database schema, because Craft is usually installed on a development environment initially, and then a DB backup is restored to production when it’s time to launch. And it’s common to have a higher version of MySQL installed locally compared to production.

[ishitatsuyuki]

Would making this change to the searchindex table have a negative effect on non-CJK languages? If not, I’d recommend you just go in and make the changes yourself.

I think it could change the search behavior and potentially include less relevant result for English-like languages, so it makes sense to keep this change to our own setup only. Since it's a MySQL built-in we should have no issue propagating this through development environments too.

We can’t really use feature detection when it comes to defining the database schema, because Craft is usually installed on a development environment initially, and then a DB backup is restored to production when it’s time to launch. And it’s common to have a higher version of MySQL installed locally compared to production.

That certainly make sense, and given that I'd suggest these searchindex recipes to be documented somewhere if they constitutes a common use-case.

[brandonkelly]

I think it could change the search behavior and potentially include less relevant result for English-like languages

In that case, I wonder if it makes sense to allow multiple search index tables, and have a config setting that maps languages to them (using searchindex by default). For example, a site with English and Japanese could use searchindex for English, and searchindex_ja for Japanese (configured to use ngram or mecab).

[ishitatsuyuki]

Makes sense. We would be happy to have this, but right not it's not very urgent as we can live with altering the schema.