A full-text index in MySQL is an index of type
FULLTEXT. Full-text indexes can be used only with
MyISAM tables, and can be created only for
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
3 types of full-text searches
IN NATURAL LANGUAGE MODEmodifier is given or if no modifier is given.
IN BOOLEAN MODEmodifier specifies a boolean search.
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSIONor
WITH QUERY EXPANSIONmodifier 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.