Introduction to full text search in MySQL

Understanding full-text search

Not all engines support full-text search As mentioned in Chapter 21, MySQL supports several basic database engines. Not all engines support full-text search as described in this book. The two most commonly used engines are MyISAM and InnoDB. The former supports full-text search, while the latter does not. This is why, although most of the sample tables created in this book use InnoDB, one sample table (the productnotes table) uses MyISAM. You should keep this in mind if your application requires full-text search functionality.

LIKE keyword, which matches text (and parts of text) using wildcard operators. Using LIKE, you can find rows that contain special values ​​or partial values, regardless of where they are within the column.

Using text-based search as a further introduction to matching column values ​​with regular expressions. Using regular expressions, you can write very complex matching patterns that find the required lines.

Although these search mechanisms are very useful, there are several important limitations.

1. Performance - Wildcard and regular expression matching usually require MySQL to try to match all rows in the table (and these searches rarely use table indexes). Therefore, these searches can be very time-consuming as the number of rows being searched increases.

2. Explicit control - With wildcards and regular expression matching, it is difficult (and not always possible) to explicitly control what is matched and what is not matched. For example, specify that a word must match, a word must not match, and a word can match or not match only if the first word actually matches.

3. Intelligent results - Although wildcard and regular expression based searches provide very flexible searches, they do not provide an intelligent method of selecting results. For example, a search for a particular word will return all rows that contain that word, without distinguishing between rows that contain a single match and rows that contain multiple matches (ordering them in order of which is likely to be a better match). Similarly, a search for a particular word will not find rows that do not contain that word but do contain other related words.

All these limitations and more can be solved with full-text search. When using full-text search, MySQL does not need to look at each row separately, and does not need to analyze and process each word separately. MySQL creates an index for each word in the specified column, and searches can be performed on these words. This way, MySQL can quickly and efficiently decide which words match (which rows contain them), which words don't match, how often they match, and so on.

The above is the detailed content of Introduction to full text search in MySQL. For more information, please follow other related articles on the PHP Chinese website!