Sorting weight problem in the jieba_chinese

[xiaoge200]

Confirmation Checklist:

• You have searched for an answer in the manual.
• You have considered using the forum for general discussions, which can be more suitable for non-urgent or broad queries.
• You are aware of our community support channels on Slack, Telegram EN, and Telegram RU, where you can interact with other users and our developers.
• You know about Manticore Team's professional services. Engaging with our experts through a support subscription can significantly accelerate resolution times and provide tailored solutions to your specific needs.

Your question:

MySQL [(none)]> create table test(title text) charset_table = 'non_cont, chinese, U+1F601, 0..9, A..Z->a..z' morphology = 'jieba_chinese' min_infix_len = '2' expand_keywords = '1';
Query OK, 0 rows affected (0.721 sec)

MySQL [(none)]> insert into test (title) values ('测试'),('测试账号');
Query OK, 2 rows affected (0.014 sec)

MySQL [(none)]> select *, weight() from test where match('测试');
+---------------------+--------------+----------+
| id                  | title        | weight() |
+---------------------+--------------+----------+
| 1228051058970853385 | 测试         |     1356 |
| 1228051058970853386 | 测试账号     |     1356 |
+---------------------+--------------+----------+
2 rows in set (0.000 sec)

document：

Question：

1. Why does the weight() result differ from the document ?
2. How can I achieve the same effect in Chinese ?

[sanikolaev]

You can experiment with ranking and index_field_lengths='1', e.g.:

mysql> drop table if exists test; create table test(title text) charset_table = 'non_cont, chinese, U+1F601, 0..9, A..Z->a..z' morphology = 'jieba_chinese' min_infix_len = '2' expand_keywords = '1' index_field_lengths='1'; insert into test (title) values ('测试'),('测试账号'); select *, weight() from test where match('测试') option ranker=sph04;
+---------------------+--------------+-----------+----------+
| id                  | title        | title_len | weight() |
+---------------------+--------------+-----------+----------+
| 1660400824146198551 | 测试         | 1         |     7356 |
| 1660400824146198552 | 测试账号     | 2         |     6356 |
+---------------------+--------------+-----------+----------+

This can be also helpful to understand how your keywords are tokenized:

mysql> call keywords('测试', 'test');
+------+-----------+------------+
| qpos | tokenized | normalized |
+------+-----------+------------+
| 1    | 测试      | 测试       |
+------+-----------+------------+

mysql> call keywords('测试账号', 'test');
+------+-----------+------------+
| qpos | tokenized | normalized |
+------+-----------+------------+
| 1    | 测试      | 测试       |
| 2    | 账号      | 账号       |
+------+-----------+------------+

[xiaoge200]

You can experiment with ranking and index_field_lengths='1', e.g.:

mysql> drop table if exists test; create table test(title text) charset_table = 'non_cont, chinese, U+1F601, 0..9, A..Z->a..z' morphology = 'jieba_chinese' min_infix_len = '2' expand_keywords = '1' index_field_lengths='1'; insert into test (title) values ('测试'),('测试账号'); select *, weight() from test where match('测试') option ranker=sph04;
+---------------------+--------------+-----------+----------+
| id | title | title_len | weight() |
+---------------------+--------------+-----------+----------+
| 1660400824146198551 | 测试 | 1 | 7356 |
| 1660400824146198552 | 测试账号 | 2 | 6356 |
+---------------------+--------------+-----------+----------+
This can be also helpful to understand how your keywords are tokenized:

mysql> call keywords('测试', 'test');
+------+-----------+------------+
| qpos | tokenized | normalized |
+------+-----------+------------+
| 1 | 测试 | 测试 |
+------+-----------+------------+

mysql> call keywords('测试账号', 'test');
+------+-----------+------------+
| qpos | tokenized | normalized |
+------+-----------+------------+
| 1 | 测试 | 测试 |
| 2 | 账号 | 账号 |
+------+-----------+------------+

select *, weight() from test where match('测试') option ranker=sph04;

It seems that adding ranker=sph04 is useful