In Part 1, we saw how MySQL provides full-text search capability via FULLTEXT indexing along with three distinct types of full-text searches. In today's blog, we'll learn how to perform Natural Language full-text searches in Navicat for MySQL.
Natural Language full-text Searching Defined
The idea behind natural language full-text searching is to seek documents (rows) that are relevant to a natural human language query such as "How natural language full-text searches work?". If you've ever used an Internet search engine like Google, this is exactly how it works!
Relevance is ascertained using a positive floating-point number of zero plus, where zero means that there is no similarity. Relevance can be based on various factors, including the number of words in the document, the number of unique words in the document, the total number of words in the collection, and the number of documents (rows) that contain a particular word.
A MySQL Natural Language Full-text Search Example
In MySQL, natural-language full-text searches are performed using the MATCH() and AGAINST() functions. The MATCH() function specifies the column where you want to search, whereas the AGAINST() function determines the search expression to be used.
The Sakila sample database represents a fictional DVD rental store. The film table contains pertinent information about each film in the store's collection. Columns include the film's title, release year, running length, and a description. Here is a sample row in Navicat's Form View. It allows you to view, update, insert, or delete data as a form, in which the current record is displayed as a field name and its value:
Indexing the description Column
In order to search the description field in full-text mode, we first have to create a full-text index on the table. We can easily do that in Navicat as follows:
- Open the description table in the Table Designer
- Select the Indexes tab.
- Click the Add Indexes button.
- Let's call our new index "idx_description".
- In the fields textbox, select the description column.
- Select FULLTEXT from the Index Type drop-down:
- Leave the Index method blank as it is not required for a FULLTEXT index.
- Finally, click the Save button to create the index.
Query Time!
Let's open up the Query Editor and write a query that will lookup rows whose description contains the phrase "Database Administrator". Navicat can help us compose our query by suggesting the names of fields even the functions we require:
Here is the final query and results for the phrase "Database Administrator". There are a surprisingly high number of films about DBAs!
Viewing the Scores
As explained above, relevance is ascertained using a positive floating-point number of zero plus, where zero means that there is no similarity. We can view the score of each record by adding the MATCH() and AGAINST() functions to the column list, for example:
This would help us to determine a cut-off point for the closest matches.
Conclusion
In today's blog, we learned how to perform Natural Language full-text searches in Navicat for MySQL.
Interested in Navicat for MySQL? You can try it for 14 days completely free of charge for evaluation purposes!
