Please advise if anyone knows whether it is possible to make the search find a record in this example. The essence of the problem is that if there is a space between letters, then the entry is not found. In this example, it is the space between “30 W”.
CREATE TABLE IF NOT EXISTS product_index_rt_87863083 (product_id uint, name string, index_text text) morphology='stem_enru' html_strip = '1' min_word_len = '1' min_infix_len = '1' expand_keywords = '1'
MySQL [(none)]> SELECT product_id, name FROM product_index_rt_87863083 WHERE MATCH('@index_text Beko RCNA 366I 30W');
+------------+--------------------+
| product_id | name |
+------------+--------------------+
| 195685 | Beko RCNA 366I 30W |
+------------+--------------------+
1 row in set (0,003 sec)
MySQL [(none)]> SELECT product_id, name FROM product_index_rt_87863083 WHERE MATCH('@index_text Beko RCNA 366I 30 W');
Empty set (0,012 sec)
Stored value in text field index_text: 195685 Beko RCNA 366I 30W Refrigerators Beko RCNA366I30W Refrigerator with Freezer Compartment
Thank you, your example works correctly, the record is successfully found in the index.
However, if the index contains more rows with different data, Manticore Search does not find the desired record. When adding the parameter distance=1 (more accurate search) to the query, the record is found without any problems. This seems illogical, because with distance=2 (the default value), there should be more options with typos than with distance=1. Or is that not the case?
I am attaching a data set in the form of an SQL dump and query examples for verification.
MySQL [(none)]> SELECT product_id, name FROM product_index_rt_87863083 WHERE MATCH('@index_text Beko RCNA 366I 30 W') option fuzzy=1, distance=2;
Empty set (0,008 sec)
MySQL [(none)]> SELECT product_id, name FROM product_index_rt_87863083 WHERE MATCH('@index_text Beko RCNA 366I 30 W') option fuzzy=1, distance=1;
+------------+--------------------+
| product_id | name |
+------------+--------------------+
| 195685 | Beko RCNA 366I 30W |
+------------+--------------------+
1 row in set (0,006 sec)
Here is another interesting example: when fuzzy search is enabled, the system does not find a record that it successfully finds when fuzzy search is disabled. However, fuzzy search should return more results. Isn’t that its main purpose?
MySQL [(none)]> SELECT product_id, name FROM test_demo WHERE MATCH('@index_text Beko RCNA 366I 30W') option fuzzy=1;
Empty set (0.006 sec)
MySQL [(none)]> SELECT product_id, name FROM test_demo WHERE MATCH('@index_text Beko RCNA 366I 30W') option fuzzy=0;
+------------+--------------------+
| product_id | name |
+------------+--------------------+
| 195685 | Beko RCNA 366I 30W |
+------------+--------------------+
1 row in set (0.003 sec)
Note: When conducting a fuzzy search via SQL, the MATCH clause should not contain any full-text operators except the phrase search operator and should only include the words you intend to match.
If I exclude the field modifier the doc is found:
mysql> SELECT product_id, name FROM product_index_rt_87863083 WHERE MATCH('Beko RCNA 366I 30W') option fuzzy=1;
+------------+--------------------+
| product_id | name |
+------------+--------------------+
| 195685 | Beko RCNA 366I 30W |
+------------+--------------------+
1 row in set (0.01 sec)
I’ll check with the dev team if we can throw an error in this case to make the behaviour less confusing.
Thank you very much for the explanation. It turns out that you cannot specify the field name in the MATCH() function.
I would like to add that I would like the search relevance to be better when using fuzzy search. In this example, the entry I am looking for is on the fifth line. I understand that this is not currently regulated by any settings?