We have configured this prefix_fields & min_prefix_len to objectId field which Index stored attributes in RT table. but this prefix search is happening to all indexed fields and not only the prefix_fields defined in match search. Is it any other settings.
If it looks like a bug, pls feel free to create an issue on Github and provide a reproducible example.
Thanks Sergey.
The following are the steps followed,
# mysql -h0 -P9306
mysql> CREATE TABLE my_table (
-> title TEXT,
-> content TEXT,
-> tags string,
-> objectId string
-> );
mysql> desc my_table;
+----------+--------+----------------+
| Field | Type | Properties |
+----------+--------+----------------+
| id | bigint | |
| title | text | indexed stored |
| content | text | indexed stored |
| tags | string | |
| objectid | string | |
+----------+--------+----------------+
mysql> INSERT INTO my_table (title, content, tags, objectId) VALUES ('Title 1', 'Some content 1', 'tag 1', 'HR-0001');
mysql> INSERT INTO my_table (title, content, tags, objectId) VALUES ('Title 2', 'Some content 2', 'tag 2', 'HR-0002');
mysql> select * from my_table where match('Title');
+---------------------+---------+----------------+-------+----------+
| id | title | content | tags | objectid |
+---------------------+---------+----------------+-------+----------+
| 2092486955806752778 | Title 2 | Some content 2 | tag 2 | HR-0002 |
| 2092486955806752777 | Title 1 | Some content 1 | tag 1 | HR-0001 |
+---------------------+---------+----------------+-------+----------+
mysql> select * from my_table where match('HR-0001');
mysql> ALTER TABLE my_table MODIFY COLUMN objectId text;
mysql> alter table my_table prefix_fields='objectId' min_prefix_len='3';
mysql> desc my_table;
+----------+--------+--------------------------+
| Field | Type | Properties |
+----------+--------+--------------------------+
| id | bigint | |
| title | text | indexed stored |
| content | text | indexed stored |
| objectid | string | indexed stored attribute |
| tags | string | |
+----------+--------+--------------------------+
mysql> show table my_table settings;
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| settings | min_prefix_len = 3 |
+---------------+--------------------+
mysql> select * from my_table where match('Title');
+---------------------+---------+----------------+----------+-------+
| id | title | content | objectid | tags |
+---------------------+---------+----------------+----------+-------+
| 2092486955806752777 | Title 1 | Some content 1 | HR-0001 | tag 1 |
| 2092486955806752778 | Title 2 | Some content 2 | HR-0002 | tag 2 |
+---------------------+---------+----------------+----------+-------+
mysql> select * from my_table where match('HR-0001');
mysql> select * from my_table where match('HR-*');
mysql> select * from my_table where match('Tit*');
mysql> INSERT INTO my_table (title, content, tags, objectId) VALUES ('Title 3', 'Some content 3', 'tag 3', 'HR-0003');
mysql> INSERT INTO my_table (title, content, tags, objectId) VALUES ('Title 4', 'Some content 4', 'tag 4', 'HR-0004');
mysql> select * from my_table where match('Title');
+---------------------+---------+----------------+----------+-------+
| id | title | content | objectid | tags |
+---------------------+---------+----------------+----------+-------+
| 2092486955806752777 | Title 1 | Some content 1 | HR-0001 | tag 1 |
| 2092486955806752779 | Title 3 | Some content 3 | HR-0003 | tag 3 |
| 2092486955806752780 | Title 4 | Some content 4 | HR-0004 | tag 4 |
| 2092486955806752778 | Title 2 | Some content 2 | HR-0002 | tag 2 |
+---------------------+---------+----------------+----------+-------+
mysql> select * from my_table where match('HR-0001');
mysql> select * from my_table where match('HR-0003');
+---------------------+---------+----------------+----------+-------+
| id | title | content | objectid | tags |
+---------------------+---------+----------------+----------+-------+
| 2092486955806752779 | Title 3 | Some content 3 | HR-0003 | tag 3 |
+---------------------+---------+----------------+----------+-------+
mysql> select * from my_table where match('HR-*');
+---------------------+---------+----------------+----------+-------+
| id | title | content | objectid | tags |
+---------------------+---------+----------------+----------+-------+
| 2092486955806752780 | Title 4 | Some content 4 | HR-0004 | tag 4 |
| 2092486955806752779 | Title 3 | Some content 3 | HR-0003 | tag 3 |
+---------------------+---------+----------------+----------+-------+
mysql> select * from my_table where match('Tit*');
+---------------------+---------+----------------+----------+-------+
| id | title | content | objectid | tags |
+---------------------+---------+----------------+----------+-------+
| 2092486955806752780 | Title 4 | Some content 4 | HR-0004 | tag 4 |
| 2092486955806752779 | Title 3 | Some content 3 | HR-0003 | tag 3 |
+---------------------+---------+----------------+----------+-------+
mysql> exit
# mysqldump --compact --replace -etc -h0 -P9306 manticore my_table | mysql -h0 -P9306
-- Warning: column statistics not supported by the server.
# mysql -h0 -P9306
mysql> select * from my_table where match('HR-*');
+---------------------+---------+----------------+----------+-------+
| id | title | content | objectid | tags |
+---------------------+---------+----------------+----------+-------+
| 2092486955806752777 | Title 1 | Some content 1 | HR-0001 | tag 1 |
| 2092486955806752778 | Title 2 | Some content 2 | HR-0002 | tag 2 |
| 2092486955806752779 | Title 3 | Some content 3 | HR-0003 | tag 3 |
| 2092486955806752780 | Title 4 | Some content 4 | HR-0004 | tag 4 |
+---------------------+---------+----------------+----------+-------+
mysql> select * from my_table where match('Tit*');
+---------------------+---------+----------------+----------+-------+
| id | title | content | objectid | tags |
+---------------------+---------+----------------+----------+-------+
| 2092486955806752777 | Title 1 | Some content 1 | HR-0001 | tag 1 |
| 2092486955806752778 | Title 2 | Some content 2 | HR-0002 | tag 2 |
| 2092486955806752779 | Title 3 | Some content 3 | HR-0003 | tag 3 |
| 2092486955806752780 | Title 4 | Some content 4 | HR-0004 | tag 4 |
+---------------------+---------+----------------+----------+-------+
Here the requirement is only for objectId field prefix search should work and title / content field should search for keywords, not prefix search.
select * from my_table where match(‘HR-'); – prefix objectId field search
and should not return result for other field title / content fields prefix search
select * from my_table where match('Tit’); – this query returning data by prefix search in title field. It should not be.
Let me know any solution.
will not help you here as manual said
The prefix_fields setting is used to limit prefix indexing to specific full-text fields in dict=crc mode
RT uses keywords dict mode it can not use dict=crc for prefix \ infix matching - only keywords. And prefix fields do not affect search - only indexing phase. On search all fields get expanded and searched for prefixes \ infixes
thanks tomat.
it’s works only for match(‘HR-'). when i try with more char after “-”, it’s not working. Like match('HR-0’). any help here?
ALTER TABLE my_table MODIFY COLUMN objectId text;
alter table my_table dict='crc';
alter table my_table prefix_fields='objectId';
mysql> select * from my_table where match('HR-*');
+---------------------+---------+----------------+----------+-------+
| id | title | content | objectid | tags |
+---------------------+---------+----------------+----------+-------+
| 2092486955806752796 | Title 2 | Some content 2 | HR-0002 | tag 2 |
| 2092486955806752797 | Title 3 | Some content 3 | HR-0003 | tag 3 |
| 2092486955806752798 | Title 4 | Some content 4 | HR-0004 | tag 4 |
| 2092486955806752795 | Title 1 | Some content 1 | HR-0001 | tag 1 |
+---------------------+---------+----------------+----------+-------+
mysql> select * from my_table where match('Tit*');
mysql> select * from my_table where match('HR-0*');
mysql> select * from my_table where match('HR-00*');
mysql> select * from my_table where match('HR-000*');
mysql> select * from my_table where match('HR-0001*');
+---------------------+---------+----------------+----------+-------+
| id | title | content | objectid | tags |
+---------------------+---------+----------------+----------+-------+
| 2092486955806752795 | Title 1 | Some content 1 | HR-0001 | tag 1 |
+---------------------+---------+----------------+----------+-------+
you do not quite get how the the Alter statement works - it saves the current indexed data as a disk chunk with the current settings then apply new settings for the RT index and new indexed data will be indexed with your new settings. Old data are not affected.
And as I said you can not use dict='crc' and prefix \ infix search for RT index.
To use prefix_fields + dict='crc' you have to use plain index. To use plain index you have to switch into non-RT mode of the config, define plain indexes at the config and data sources for these plain indexes, disable data_dir.
Index your data into indexes with indexer then load indexes into daemon. Then you should see that only objectId field data was expanded on indexing phase and got matched by the wildcards queries on search.
Thanks tomat for your prompt response. Yes i have reindexed after the Alter statement for old data.
So prefix will not work for RT index with prefix_fields for a specific field defined and we have to use Plain index.
yes you can not use prefix_fields for RT index and should try the plain index for that
1 Like