MySQL Bugs: #38827: Fulltext index doesn't work where in table are less than three records

Bug #38827	Fulltext index doesn't work where in table are less than three records
Submitted:	15 Aug 2008 18:12	Modified:	16 Aug 2008 8:54
Reporter:	Michal Prokopiuk	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S3 (Non-critical)
Version:	5.0.51a	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	fulltext

Description:
Fulltext search doesn't work, where i have less than three records in table. When I add four record i can search.

CREATE TABLE `referers` (
  `id` int(11) NOT NULL auto_increment,
  `referer` mediumtext,
  `hash` varchar(32) default NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `idx_hash` (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2

mysql> select * from referers;
+----+----------------------------------------------------------------------------+----------------------------------+
| id | referer                                                                    | hash                             |
+----+----------------------------------------------------------------------------+----------------------------------+
|  1 | http://address/ | teiqbrnrnwrdswfkbyhxjalhgkloraep |
+----+----------------------------------------------------------------------------+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT referer  FROM referers  WHERE MATCH (hash) AGAINST ('teiqbrnrnwrdswfkbyhxjalhgkloraep');
Empty set (0.00 sec)

mysql> insert into referers(referer, hash) values('dupa', 'jasia');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT referer  FROM referers  WHERE MATCH (hash) AGAINST ('teiqbrnrnwrdswfkbyhxjalhgkloraep');
Empty set (0.00 sec)

mysql> SELECT referer  FROM referers  WHERE MATCH (hash) AGAINST ('jasia');
Empty set (0.00 sec)

mysql> insert into referers(referer, hash) values('dupa1', 'jasia1');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT referer  FROM referers  WHERE MATCH (hash) AGAINST ('jasia1');
+---------+
| referer |
+---------+
| dupa1   |
+---------+
1 row in set (0.00 sec)

mysql> SELECT referer  FROM referers  WHERE MATCH (hash) AGAINST ('jasia');
+---------+
| referer |
+---------+
| dupa    |
+---------+
1 row in set (0.00 sec)

mysql> SELECT referer  FROM referers  WHERE MATCH (hash) AGAINST ('teiqbrnrnwrdswfkbyhxjalhgkloraep');
+----------------------------------------------------------------------------+
| referer                                                                    |
+----------------------------------------------------------------------------+
| http://address/ |
+----------------------------------------------------------------------------+
1 row in set (0.01 sec)

How to repeat:
CREATE TABLE `referers` (
  `id` int(11) NOT NULL auto_increment,
  `referer` mediumtext,
  `hash` varchar(32) default NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `idx_hash` (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;

insert into referers(referer, hash) values('test', 'test1');
SELECT referer  FROM referers  WHERE MATCH (hash) AGAINST ('test1');

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html about details how full-text search works. Regarding to this case:

The 50% threshold has a significant implication when you first try full-text searching to see how it works: If you create a table and insert only one or two rows of text into it, every word in the text occurs in at least 50% of the rows. As a result, no search returns any results. Be sure to insert at least three rows, and preferably many more. Users who need to bypass the 50% limitation can use the boolean search mode; see Section 11.8.2, “Boolean Full-Text Searches”.

Thank you for Your answer.