Introduction
It is a technique for searching a computer-stored document or database. The search engine examines all words in the record that might not perfectly match the search criteria in a fulltext search. The records consist of textual data like product descriptions, blog posts, articles, etc.
Types of full-text searches
Natural Language Fulltext Searches
Full-text searching interprets the search string as a free text using MATCH() and AGAINST functions. The MATCH() function specifies the column you want to search and explores a string against a text collection, and the AGAINST() function determines the search expression to be used.
Code
|
SELECT * FROM table_name WHERE MATCH(col1, col2) AGAINST('search terms' IN NATURAL LANGUAGE MODE) |
Example
|
mysql> CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(150), body TEXT, FULLTEXT (title,body) ) ENGINE=InnoDB; |
| Query OK, 0 rows affected (0.08 sec) |
Boolean Fulltext Searches
A boolean search handles the search string using IN BOOLEAN MODE modifier in which the string contains the words to search for and certain characters have special meaning at the beginning or end of words in the search string. In this type of query, the + and - operators indicate that a word should be present or absent, respectively, for a match to occur. Thus, the query fetches all the rows with the word “Joins” but does not contain the word “right”.
Code
|
SELECT * FROM table_name WHERE MATCH(col1, col2) AGAINST('search terms' IN BOOLEAN MODE); |
Example
| mysql> SELECT * FROM tutorial WHERE MATCH(title, description) AGAINST ('+Joins -right' IN BOOLEAN MODE); |
Query Expansion Search Type
It is a natural language search type modification that performs the search twice. This is commonly used when a search phrase is too short, including a few most specific documents after performing the natural language search.
Example
|
SELECT * FROM posts WHERE MATCH(title, descriptions) AGAINST('MySQL' WITH QUERY EXPANSION); |
85+ registered 