ALTER TABLE books ADD FULLTEXT(title);
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 relevantMATCH (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.SELECT title, MATCH(title) AGAINST('harry potter' IN BOOLEAN MODE) AS Score FROM books WHERE MATCH(title) AGAINST ('harry potter' IN BOOLEAN MODE);
[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
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`;