Unlike a regular index, MySQL’s FULLTEXT allows super-fast searches of entire columns of text. It stores every word in every data string in a special index that you can search using “natural language,” in a similar manner to using a search engine.
It’s not strictly true that MySQL stores all the words in a FULLTEXT
index, because it has a built-in list of more than 500 words that it
chooses to ignore because they are so common that they aren’t very
helpful for searching anyway—so-called stopwords. This list
includes the, as, is, of, and so on. The list helps MySQL run much
more quickly when performing a FULLTEXT search and keeps database
sizes down.
Here are some things that you should know about FULLTEXT indexes:
- Since MySQL 5.6, InnoDB tables can use FULLTEXT indexes, but prior to that FULLTEXT indexes could be used only with MyISAM tables. If you need to convert a table to MyISAM, you can usually use the MySQL command ALTER TABLE tablename ENGINE = MyISAM;.
- FULLTEXT indexes can be created for CHAR, VARCHAR, and TEXT columns only.
- 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 than to load data into a table that has an existing FULLTEXT index.
To create a FULLTEXT index, apply it to one or more records, which adds a FULLTEXT index to the pair of columns author and title in the classics
table (this index is in addition to the ones already created and does not affect them).
ALTER TABLE classics ADD FULLTEXT(author,title);
You can now perform FULLTEXT searches across this pair of columns.
Hint, if you find that MySQL is running slower than you think it should
be when accessing your database, the problem is usually related to
your indexes. Either you don’t have an index where you need one,
or the indexes are not optimally designed. Tweaking a table’s
indexes will often solve such a problem.