Improving search for Chinese characters

luceos

clarkwinkelmann Anyone happens to know ?

https://github.com/flarum/core/blob/aba291c542b1095fe958650a117602048dd673d1/js/src/forum/components/Search.js#L156

fromsly

clarkwinkelmann I found that the Chinese search for this forum is nice: https://freeerror.org/ ——A website built with Flarum program. Its principle is that a single Chinese character cannot be searched, but it can be found from two Chinese characters.

But sometimes, Chinese people also search for a single character. For example, the Chinese character "书"，It means "book" in English.

fromsly

Doesn't Chinese user care about this problem?

Littlegolden

fromsly Yeah we cares, but isn't the webmaster of freeerror.org posting a way to optimize Chinese search？
关心的，不过 freeerror.org 的站长不是发帖说了优化中文搜索的方法吗？

clarkwinkelmann

luceos there's also no results when loading https://discuss.flarum.org/?q=%E5%A4%A7%E5%AE%B6 with preloaded discussions, so it must be defined somewhere server-side as well.

Littlegolden

fromsly

I have tested this as a workable solution:

🇺🇸 MySQL supports Chinese and Japanese FULLTEXT indexing since version 5.7. Using the optimization below, I believe that full-text indexing can be built for most languages.
In addition, Flarum developers were stated that the ElasticSearch distributed full-text search engine will be used in the future version BUT till now, ElasticSearch haven't been used. Please also note that this method will affect database performance to some extent.

🇨🇳 MySQL 自 5.7 开始支持中文、日文的全文索引。使用下面的优化，我相信可以建立大多数语言的全文索引。
另外，Flarum开发者也曾说过，在将来的版本会使用 ElasticSearch 分布式全文搜索引擎，因为 ElasticSeatch 比此方法好，但直到现在也没有用上。请注意，此方法会在一定程度上影响数据库性能。

Find your MySQL configuration file（找到数据库配置文件）：

The path of my configuration file on the server（我的配置文件在服务器上的路径）:
/etc/my.cnf

Add the following code under "[Mysqld]" in the configuration file（在配置文件中加入以下内容）：

[mysqld]
ngram_token_size=2

Go to phpMyAdmin to re-index（前往 phpMyAdmin 重建数据库索引。）
Perform the following two paragraphs separately（分别执行以下两段）：

USE YourFlarumDataBaseName;  // 这里替换为你的 Flarum 数据库的名字
ALTER TABLE posts DROP INDEX content;
CREATE FULLTEXT INDEX content ON `posts` (`content`) WITH PARSER ngram;

USE YourFlarumDataBaseName;  // 这里替换为你的 Flarum 数据库的名字
ALTER TABLE discussions DROP INDEX title;
CREATE FULLTEXT INDEX title ON `discussions` (`title`) WITH PARSER ngram;

Then we done! Wait for a while and force the browser to refresh to see the effect.（完成！等待一会儿，强制刷新浏览器，查看效果。）

🇨🇳 Chinese search

🇯🇵 Japanese search

🇺🇸 English search

samtto

Littlegolden

Thank you very much.Can u show me how to set it under Windows 2012r2 IIS mysql5.6?
I tried the way above, but failed.tks.
（在WIN12 IIS环境下需要如何设置呢？感谢）

Littlegolden

samtto Sorry, I forgot to tell you that this search optimization is only supported in MySQL 5.7 or higher version.

samtto

samtto OK
now,something went wrong,than search for something shows mistake.how to went back?
tks

Littlegolden

samtto
This is not very clear. I hope you can learn the lesson. Whenever you have to modify the database and server configuration, make a backup in advance. . .
You can now try to delete the line of code added in the database, and then click the index table in phpmyadmin to rebuild the index.

这个就不是很清楚了，希望你能吸取教训，以后但凡有修改数据库、服务器配置的时候，提前做好备份。。。
你现在先创建服务器快照（备份服务器），然后试试把数据库增加的那一行代码删掉，然后在phpmyadmin 中用鼠标直接点击索引表的方法重建索引。
下面的GIF是我演示的对之前重建索引的 discussion - title 表再次重建索引，对于 post - content 表也是同样的方法：

samtto

Littlegolden
Thanks for your kindness.I upgraded MYSQL to 5.7 and created a new website, following the tutorial above, The SQL execution was successful.but the results turn out the same.
Restore success, wait for system upgrade to resolve the problem，Maybe there's something wrong with my system environment.

fromsly

Littlegolden When is the next version coming?

samtto

fromsly 2020/02 maybe

clarkwinkelmann

Neither SQL search changes nor ElasticSearch is currently on Flarum's TODO for beta 12 as far as I know.

If someone can provide a concrete suggestion of change along with tests showing how it affects search performance, we can consider doing the change. PR welcome.

In the meantime it might be good to open an issue for this on GitHub with maybe some suggestions on how to differentiate between queries that are too short or not too short depending on the character set.

samtto

Basic on windows2018r2.IIS8.5.9 .I created a new website to test whether the Chinese search function can be implemented in MYSQL5.7 environment through the above methods. The search results still cannot be implemented in Chinese,
environmental affect? Then I found that PHP lacks support for tokenizer.But I'm not sure if the tokenizer affected this result.I will keep the test site until the problem is resolved.thank you very much!

clarkwinkelmann fromsly Littlegolden

Littlegolden

samtto 那你试试不用UI操作，直接在phpMyadmin里执行SQL语句。

Then you try to execute SQL statements directly in phpMyadmin without UI operation.

clarkwinkelmann

Littlegolden please also include an English version of your post as per our community guidelines. Thanks.

samtto

Littlegolden yes i do it without ui operation at the first time.And I also confirmed the result through MYADMIN.

post - content is ok too.

samtto

Littlegolden mysql ver:mysql5.7.28,and i just know, only support versions above 5.7.6 ？
🤣