Summary: in this tutorial, we will introduce you to MySQL full-text search and its features.
MySQL supports text searching by using the LIKE operator and regular expression. However, when the text column is large and the number of rows in a table is increased, using these methods has some limitations:
- Performance: MySQL has to scan the whole table to find the exact text based on a pattern in the
LIKEstatement or pattern in the regular expressions.
- Flexible search: with the
LIKEoperator and regular expression searches, it is difficult to have a flexible search query e.g., to find product whose description contains
- Relevance ranking: there is no way to specify which row in the result set is more relevant to the search terms.
Because of these limitations, MySQL extended a very nice feature so-called full-text search. Technically, MySQL creates an index from the words of the enabled full-text search columns and performs searches on this index. MySQL uses a sophisticated algorithm to determine the rows matched against the search query.
The following are some important features of MySQL full-text search:
- Native SQL-like interface: you use the SQL-like statement to use the full-text search.
- Fully dynamic index: MySQL automatically updates the index of text column whenever the data of that column changes.
- Moderate index size: it doesn’t take much memory to store the index.
- Last but not least, it is fast to search based on complex search queries.
Notice that not all storage engines support the full-text search feature. In MySQL version 5.6 or later, only MyISAM and InnoDB storage engines support full-text search.