0

We maintain a table that looks like this to perform search queries on our forums database. I'll draw your attention to the FULLTEXT columns:

CREATE TABLE `forums_posts_search` (
  `post_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `topic_id` bigint(20) NOT NULL,
  `topic_title` varchar(255) NOT NULL,
  `post` text NOT NULL,
  PRIMARY KEY (`post_id`),
  FULLTEXT KEY `search` (`topic_title`,`post`)
) ENGINE=MyISAM AUTO_INCREMENT=1285900 DEFAULT CHARSET=latin1;

For a row that has the following forums_posts_search.post value:

INSERT INTO `forums_posts_search` (`post_id`, `user_id`, `topic_id`, `topic_title`, `post`)
VALUES
(1, 1, 1, 'Hashtag Test', 'Hi, <br />\n<br />\nMine #HME as the #BC don\'t work, too. <br />\n<br />\nI had to send a ticket about this ?<br />\n');

the following query does not return the result when a user goes to search for the parameters "#hme" or "#bc":

SELECT [...], MATCH(p.topic_title, p.post) AGAINST ("#hme") as score
FROM forums_posts_search p
INNER JOIN forums_topics t ON (p.topic_id = t.topic_id)
INNER JOIN forums f ON (t.forum_id = f.forum_id)
LEFT OUTER JOIN users u ON (p.user_id = u.id)
WHERE MATCH(p.topic_title, p.post) AGAINST ("#hme")
ORDER BY score

Running MariaDB on top of MySQL 5.5.44. I recently rebuilt the FULLTEXT indexes manually and then ran a REPAIR TABLE [...] QUICK; but the results are still the same.

Why isn't this query returning records where forums_posts_search.post contains "#hme" or "#bc"? Strangely, a longer search parameter (i.e. "#hashtag") returns all of the results.

0

Change the ft_min_word_len value to specify the minimum length of the word to be included in a MyISAM FULLTEXT index.

ft_min_word_len

The minimum length (default: 4) of the word to be included in a MyISAM FULLTEXT index.

Note: FULLTEXT indexes on MyISAM tables must be rebuilt after changing this variable. Use REPAIR TABLE tbl_name QUICK.

Source: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_ft_min_word_len

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy