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.
