MySQL Full-Text Search

A full-text index in MySQL is an index of type FULLTEXT. Full-text indexes can be used only with  InnoDB  or  MyISAM  tables, and can be created only for  CHAR ,  VARCHAR , or  TEXT  columns. MySQL provides a built-in full-text ngram parser that supports Chinese, Japanese, and Korean (CJK), and an installable MeCab full-text parser plugin for Japanese.  A FULLTEXT index definition can be given in the  CREATE TABLE  statement when a table is created, or added later using  ALTER TABLE  or  CREATE INDEX . For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.

3 types of full-text searches

  • A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). Full-text searches are natural language searches if the IN NATURAL LANGUAGE MODE modifier is given or if no modifier is given. 
  • A boolean search interprets the search string using the rules of a special query language. The string contains the words to search for. It can also contain operators that specify requirements such that a word must be present or absent in matching rows, or that it should be weighted higher or lower than usual. Certain common words (stopwords) are omitted from the search index and do not match if present in the search string. The IN BOOLEAN MODE modifier specifies a boolean search. 
  • A query expansion search is a modification of a natural language search. The search string is used to perform a natural language search. Then words from the most relevant rows returned by the search are added to the search string and the search is done again. The query returns the rows from the second search. The IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION or WITH QUERY EXPANSION modifier specifies a query expansion search. 

 

Create full-text indexes

// Add FULLTEXT KEY on table creation
CREATE TABLE user (
  id INT NOT NULL AUTO_INCREMENT,
  first_name varchar(255) DEFAULT NULL,
  last_name varchar(255) DEFAULT NULL,
  email varchar(255) DEFAULT NULL,
  PRIMARY KEY (id),
  FULLTEXT KEY (first_name,last_name,email)
);
ALTER TABLE user  
ADD FULLTEXT(first_name,last_name,email);

Note that once you full-text index a table, you have to specify all full-text indexed columns of that table on your query.

SELECT * FROM user
WHERE MATCH (first_name,last_name,email)
AGAINST ('folau' WITH QUERY EXPANSION);

It’s important to know that while this is working. It is not a “go to” as for a production searching functionality. Elasticsearch or other search engines are better for that. I would use mysql full-text for a small application but as that small application grows, I would move searching to a search engine like Elasticsearch.




Subscribe To Our Newsletter
You will receive our latest post and tutorial.
Thank you for subscribing!

required
required


Leave a Reply

Your email address will not be published.