Improving search for Chinese characters

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环境下需要如何设置呢？感谢）

fromsly

Littlegolden When is the next version coming?

vmomenv

Littlegolden 我尝试着这样做，但仅仅是部分有效

lichengkun135

Littlegolden Hi, I'm wondering can this method work with freeflarum?

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.

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

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.

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 ？
🤣

Littlegolden

samtto no, the version 5.7.26 is supported that i'm using.

samtto

Littlegolden
still can't search chinese ,is there something wrong with "my.ini" help me find out?


[client]
#绑定IPv4和3306端口
bind-address = 120.76.77.119
port = 3306

[mysql]
no-beep

# default-character-set=
default-character-set=utf8
[mysql.server]
default-character-set=utf8
[mysql_safe]
default-character-set=utf8
[mysqld]
port=3306
ngram_token_size=2
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb

# mysql根目录
basedir="e:\mysql\"

# 放所有数据库的data目录
datadir="e:\\mysql\\data"
character-set-client-handshake = false
character-set-server = utf8
init_connect='SET NAMES utf8'
# character-set-server=

# 默认存储引擎innoDB
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

#日志输出为文件
log-output=FILE

# 是否开启sql执行结果记录，必须要设置general_log_file参数，日志的路径地址
# 即日志跟踪，1为开启，0为关闭
general-log=1
general_log_file="execute_sql_result.log"

# 配置慢查询，5.7版本默认为1
slow-query-log=1
slow_query_log_file="user-slow.log"
long_query_time=10

#默认不开启二进制日志
#log-bin=mysql-log

#错误信息文件设置，会将错误信息放在data/mysql.err文件下
log-error=mysql.err

# Server Id.数据库服务器id，这个id用来在主从服务器中标记唯一mysql服务器
server-id=1

#lower_case_table_names： 此参数不可以动态修改，必须重启数据库
#lower_case_table_names = 1  表名存储在磁盘是小写的，但是比较的时候是不区分大小写
#lower_case_table_names=0  表名存储为给定的大小和比较是区分大小写的 
#lower_case_table_names=2, 表名存储为给定的大小写但是比较的时候是小写的
lower_case_table_names=1

#限制数据的导入导出都只能在Uploads文件中操作,这个是在sql语句上的限制。
#secure-file-priv="D:\AppServ\mysql-5.7.23/Uploads"
#值为null ，也就是注释掉这个参数或者secure-file-priv=null。表示限制mysqld 不允许导入|导出
#值为/tmp/ ，即secure-file-priv="/tmp/" 表示限制mysqld 的导入|导出只能发生在/tmp/目录下
#没有具体值时，即secure-file-priv=      表示不对mysqld 的导入|导出做限制

# 最大连接数
max_connections=600
# 打开表的最大缓存数
table_open_cache=2000

# tmp_table_size 控制内存临时表的最大值,超过限值后就往硬盘写，写的位置由变量 tmpdir 决定 
tmp_table_size=16M

# 每建立一个连接，都需要一个线程来与之匹配，此参数用来缓存空闲的线程，以至不被销毁，
# 如果线程缓存中有空闲线程，这时候如果建立新连接，MYSQL就会很快的响应连接请求。
# 最大缓存线程数量
thread_cache_size=10

#*** INNODB Specific options InnoDB存储引擎的配置

# InnoDB表的目录共用设置。没有在 my.cnf 进行设置，InnoDB 将使用MySQL的 datadir 目录为缺省目录。
# 如果设定一个空字串,可以在 innodb_data_file_path 中设定绝对路径
#innodb_data_home_dir=

# 通常设置为 1，意味着在事务提交前日志已被写入磁盘， 事务可以运行更长以及服务崩溃后的修复能力。
# 如果你愿意减弱这个安全，或你运行的是比较小的事务处理，可以将它设置为 0 ，以减少写日志文件的磁盘 I/O。
innodb_flush_log_at_trx_commit=1

# InnoDB 将日志写入日志磁盘文件前的缓冲大小
innodb_log_buffer_size=1M

# InnoDB 用来高速缓冲数据和索引内存缓冲大小。
innodb_buffer_pool_size=1000M

innodb_log_file_size=48M

# InnoDB 会试图将 InnoDB 服务的使用的操作系统进程小于或等于这里所设定的数值。默认为8
innodb_thread_concurrency=9

innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0

back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

note: base on mysql5.7.28
PHP extensions all ready,Mysql has been upgraded from 5.6 to 5.7,so i guest,someting wrong with my.ini.anyone knows?thank you!

Johnshi

POST http://xx.xx.xx.x/api/extensions/jjandxa-chinese-search

{
"errors": [
{
"status": "500",
"code": "unknown",
"detail": "[XSErrorException] ../vendor/hightman/xunsearch/lib/XSServer.class.php(476): fsockopen(): unable to connect to localhost:8383 (Cannot assign requested address)(2)"
}
]
}

Johnshi

Johnshi I get this error in [vps]

« Previous Page Next Page »