MySQL Fulltext Search

Posted 5 years ago - updated 3 years ago mysql

Creating

ALTER TABLE books ADD FULLTEXT(title);

Querying

Boolean mode

  • harry potter Match either or both words
  • +harry +potter Match both words
  • +harry -potter Match Harry not Potter
  • +harry ~potter Match Harry and mark as less relevant if a row contains Potter
  • +pot* Match Potter,pottery,pot etc
  • "harry potter" Match Harry Potter exactly
  • +harry +(potter prisoner) Match Harry Potter or Harry Prisoner
  • +harry +(>potter <prisoner) Match either "harry potter" or "harry prisoner", with rows matching "harry potter" more relevant
  • MATCH (title) AGAINST ('+harry*' IN BOOLEAN MODE) means that you're searching for rows where a word in the title column must contain and begin + with the word Harry.

Score

SELECT title, MATCH(title) AGAINST('harry potter' IN BOOLEAN MODE) AS Score FROM books WHERE MATCH(title) AGAINST ('harry potter' IN BOOLEAN MODE);

Fix the minimum word length

[mysqld]
ft_min_word_len=3
ft_max_word_len=20

note when changing these configuration setting you will need to rebuild the table with REPAIR TABLE tablename

For Innodb

Drop index:

alter table `table` DROP INDEX indexname;

ALTER TABLE `table` ADD FULLTEXT INDEX (`field1`, `field2`);

In my.cnf set:

innodb_ft_min_token_size=2

Also worth:

OPTIMIZE TABLE `tablename`;

fine tuning official doc