Summary: In this tutorial, you will learn how full-text search is implemented in MySQL and its features.
MySQL supports text search by using standard SQL LIKE statement and regular expression. However, when the text column in a table is large and number of rows increase dramatically, those methods have their own limitations:
- Performance: MySQL has to scan the whole table to match row by row to find the exact text based on pattern in SQL LIKE or pattern match a given regular expression.
- Flexible search: With SQL LIKE and regular expression search, it is difficult to have flexible search query. For instance, find all products which product description has word "car" but not the word "classic".
- Relevance ranking: There is no way to specify what rows in the result set is matched to the search query most.
Because of those limitations above, MySQL extended a very nice feature which is so-called full-text search. Technically, MySQL indexes words of enabled full-text search column and effectively performs search based on this index. MySQL engine then uses sophisticated algorithm to determine what row is matched most to the search query and so on.
Here are some important features of MySQL full-text search:
- Native SQL-like interface: you use SQL-like statement to use the full-text search.
- Fully dynamic index: MySQL automatically index the text column whenever the data changes. You don’t need to run the index periodically.
- Moderate index size: it does not take much memory to store the index.
- Last but not least, it is fast to search based on complex search query.
Note that not all storage engines support full-text searching. At the time of writing this tutorial, only MyISAM storage engines support full-text search.