kahredici kahredici - 1 month ago 9
MySQL Question

Interesting full-text-search Behaviour

Crating table

CREATE TABLE `Footable` (
`id` int NOT NULL AUTO_INCREMENT,
`foo_colmn` varchar(255) NOT NULL,
FULLTEXT (foo_colmn),
PRIMARY KEY (`id`)) ENGINE=MyISAM;


Insert some row

INSERT INTO Footable (`foo_colmn`) VALUES ("against1111 , against");
INSERT INTO Footable (`foo_colmn`) VALUES ("against1111");
INSERT INTO Footable (`foo_colmn`) VALUES ("against");


And search with BOOLEAN MODE

SELECT * FROM Footable WHERE MATCH
(foo_colmn)
AGAINST
('against1111' IN BOOLEAN MODE);


-------------------------
id |foo_colmn |
---|--------------------|
1 |against1111 , against |
2 |against1111 |


Yeah thats prity normal but if i search the "against"

SELECT * FROM Footable
WHERE MATCH
(foo_colmn)
AGAINST
('against' IN BOOLEAN MODE);

---------------
id |foo_colmn |
---|----------|


Return nothing. I can't understand what's going on. (Sory about my poor English)

Answer

You need to learn about stop words (and short words).

Stop words are words that are common or deemed not useful, so they are removed from the index. There is a standard list included with MySQL. "Against" would be a typical stop word (and "is", "the", "a", "nevertheless", etc.).

You can read about them here.

The solution is to rebuild the full text index with no stop words (or with a custom list).

Edit.

I should note that for some (now doubt brilliant) reason, InnoDB stop words are different from MyISAM stop words. So, I can make a guess that you areusing MyISAM, because "against" is in only one list.

Comments