CODEWITHSUNDEEP
mysqlsqlfull-text-search
Gabriel
Gabriel

Reputation: 969

Fulltext search in mysql doesn't retrieve all rows

i've a problem with a query in mysql.

This is what i done:

CREATE TABLE `dar`.`MyTable` (
  `MyCol` VARCHAR(100) NOT NULL,
  FULLTEXT INDEX `Index_1`(`MyCol`)
) 
ENGINE = MyISAM;

INSERT INTO MyTable (MyCol)
VALUES ('6002.C3'),
       ('6002'),
       ('6002R1'),
       ('6003.C4'),
       ('AA6002.X'),
       ('BB 6002.X');

This is not necessary, but i've done anyway:

REPAIR TABLE MyTable QUICK;

Now, i execute the next query:

SELECT MyCol FROM MyTable
WHERE MATCH(MyCol) AGAINST ('6002*');

And, it doesn't return any row!!

The parameter ft_min_word_len i've changed to 2, but nothing is changed.

When deleting the row with 'BB 6002.X' the query returns 2 rows!!

6002
6002.C3

That is creepy.

Any idea what is happening here?

I need the query return:

6002.C3
6002
6002R1

Plus if include:

AA6002.X
BB 6002.X

Thanks in advance!!

Upvotes: 0

Views: 68

Answers (1)

BK435
BK435

Reputation: 3176

You are past the 50% threshold in your dataset. Try

SELECT MyCol FROM MyTable
WHERE MATCH(MyCol) AGAINST ('6003');

And see what the result is.

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 12.9.2, “Boolean Full-Text Searches”.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

Upvotes: 2

Related Questions