MySQL Forums
Forum List  »  Newbie

Re: Inconsistency in Mysql Full text Indexing
Posted by: Peter Brawley
Date: June 11, 2019 11:22AM

I can reproduce the problem in 5.7 and in 8.0. Match returns 0 for all but ending substrings. Why, I dunno. Probably someone who knows more about fulltext will spot the difficulty. To simplify, a test table & query, innodb_ft_min_token_size is set to 1 less than your shortest target substring length ...

DROP TABLE IF EXISTS `tft`;
CREATE TABLE `tft` (
  `f1` varchar(64) NOT NULL,
  `tenant_id` varchar(64) DEFAULT NULL,
  `F_400011_0` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`f1`),
  FULLTEXT KEY `F_400011_0` (`F_400011_0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tft` VALUES 
('client_5_009eda6c-dd43-4cd8-83c6-3899afcccf62','client_5','vapp148-sjc'),
('client_5_00a57d10-d688-45de-8182-c7a23a6d7594','client_5','vapi147-sjc');

select * from tft;
+-----------------------------------------------+-----------+-------------+
| f1                                            | tenant_id | F_400011_0  |
+-----------------------------------------------+-----------+-------------+
| client_5_009eda6c-dd43-4cd8-83c6-3899afcccf62 | client_5  | vapp148-sjc |
| client_5_00a57d10-d688-45de-8182-c7a23a6d7594 | client_5  | vapi147-sjc |
+-----------------------------------------------+-----------+-------------+
select @@innodb_ft_min_token_size;
+----------------------------+
| @@innodb_ft_min_token_size |
+----------------------------+
|                          2 |
+----------------------------+
select 
  f_400011_0, 
  f_400011_0 like '%sjc' as likesjc,
  match (f_400011_0) against ('sjc' in natural language mode) as matchsjc,  
  f_400011_0 like '%148%' as like148,
  match (f_400011_0) against ('148' in natural language mode) as match148,  
  f_400011_0 like 'vapp%' as likevapp,
  match (f_400011_0) against ('vapp' in natural language mode) as matchvapp
from tft 
where tenant_id = 'client_5' ;
+-------------+---------+----------------------------+---------+----------+----------+-----------+
| f_400011_0  | likesjc | matchsjc                   | like148 | match148 | likevapp | matchvapp |
+-------------+---------+----------------------------+---------+----------+----------+-----------+
| vapp148-sjc |       1 | 0.000000001885928302414186 |       1 |        0 |        1 |         0 |
| vapi147-sjc |       1 | 0.000000001885928302414186 |       0 |        0 |        0 |         0 |
+-------------+---------+----------------------------+---------+----------+----------+-----------+

Options: ReplyQuote


Subject
Written By
Posted
Re: Inconsistency in Mysql Full text Indexing
June 11, 2019 11:22AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.