MySQL Full-Text Search

MySQL full-text search is a powerful tool for effectively seeking data in databases. It allows creating complete text indexes that simplify search query performance. It's relevant while working with textual content, especially when you need to quickly find corresponding information using various search modes such as Natural Language, Query Expansion, and Boolean.

With dbForge Studio for MySQL, users can execute complex search queries efficiently. The tool seamlessly integrates with existing MySQL databases and eliminates the need for complicated setup procedures or data migration processes. Users can leverage its full-text search capabilities within their existing database infrastructure without significant disruptions to their workflows.

Overview of the full-text search capabilities in MySQL

With the full-text search functionality, you can run complex queries against a collection of data. It's designed to search for words and phrases in a large set of information, making it particularly useful for applications where text content needs to be explored and matched.

There are different types of full-text search in MySQL that enable users to configure and optimize their search queries. The most common ones are Natural Language, Boolean, and Query Expansion.

Natural Language allows inputting questions or words just like in regular speech. The system recognizes the context and tries to find the required results using natural language processing algorithms.
Boolean Search makes it possible to use logical operators (AND, OR, NOT) to precisely define search conditions. It helps create compound queries, taking into account the logic of interaction between keywords.
Query Expansion permits broadening the search query by adding similar and related terms to it. The aim is to improve the relevance of results by considering synonyms and relationships between words.

The MyISAM and InnoDB storage engines have some differences in delivering search results, as well as in approaches to optimization and ensuring data reliability. The choice between them depends on the specific requirements and characteristics of the project. If fast read speed is crucial and there are not many concurrent writes, MyISAM can be effective. In cases where transactional support and reliability are important, InnoDB is often a better choice.

How to create a MySQL full-text index

Let's see how it's easy to create a full-text index in dbForge Studio for MySQL. We're going to create indexes for the district and postal_code columns in the address table with the help of this query:

ALTER TABLE address ADD FULLTEXT INDEX `fulltext`(district, postal_code);

To check that the indexes have been created, run the following statement:

SHOW INDEXES FROM adddress;

Full-text search: natural language

Natural language is one of the available modes for searching data stored in a MySQL database. This mode is used to interpret search queries in a way similar to humans, allowing for more intuitive and flexible search capabilities. Here is a detailed explanation of how the natural language mode works.

Understanding of search queries

In this mode, MySQL detects and analyzes a query in order to retrieve its intended meaning, rather than simply matching specific keywords.

Tokenization and stemming

When you run a search query, MySQL splits it into separate words or phrases. Additionally, stemming is performed to reduce words to their root form. For example, running can be reduced to just run. Therefore, the search becomes more comprehensive.

Stop words processing

Stop words are common words such as and, or, the, etc., which are often ignored in search queries. In the natural language mode, MySQL intelligently processes these words, considering their importance in the context of a search query.

Phrase search

Natural language also supports phrase search, where users can enclose phrases in double quotes to search for exact matches of the entire phrase rather than individual words.

Language support

The mode sustains multiple languages to allow performing full-text search in different languages with corresponding language tokenization and stemming rules.

Boolean search

The natural language mode supports Boolean operators such as AND, OR, and NOT. It helps create more complex queries to clarify search results.

Suppose we have the film table with the film_id, title>, and description columns. We want to perform a full-text search using the natural language mode to find the word beautiful in title or description. We can do it with this query:

SELECT film_id, title, description
  FROM film
 WHERE MATCH (title, description) AGAINST ('beautiful' IN NATURAL LANGUAGE MODE);

Full-text search: the query expansion mode

Query expansion is a way to obtain information that improves search results by adding extra terms to a search query. With this mode, MySQL automatically includes synonyms and semantically related terms in the initial search query. Query expansion can help include more related data in search results and improve their relevance.

Let's try this mode in practice and see what the result will be. We're going to search for records in the film table where either the title or the description column contains the term drama or closely related terms.

SELECT film_id, title, description
  FROM film
 WHERE MATCH (title, description) AGAINST ('+drama' WITH QUERY EXPANSION);

Full-text search: the boolean mode

Boolean in MySQL allows searching with the help of the boolean operators such as AND, OR, and NOT to establish search conditions that must be met to find relevant records. It means that you can set precise and complex search conditions to find the required data in databases. This is particularly useful for seeking specific phrases or excluding unwanted results.

This query initiates the search for data that includes beautiful but does not contain the term boring in the title or description columns.

SELECT film_id, title, description
  FROM film
 WHERE MATCH (title, description) AGAINST ('+beautiful -boring' IN BOOLEAN MODE);

Advantages of GUI tools for database searching

GUI tools for searching offer quite enough benefits.

Ease of use: Most instruments have a user-friendly interface. Instead of writing queries manually, users can interact with visual elements such as forms, query builders, etc.
Query optimization: GUI tools have built-in features for enhancing queries, for example, execution plans, analysis capabilities, and performance monitoring.
Data visualization: Obtained data is processed and analyzed more easily through the use of charts, graphs, and pivot tables.
Security: GUI tools include various safety measures that help protect sensitive information and ensure that only authorized users can access databases.
Cross-platform compatibility: Many instruments can be run on Windows, macOS, and Linux. This flexibility allows users to work from different devices and environments.
Integration: GUI tools are often compatible with other software and services. This approach streamlines workflows and enables additional functionality.

Search objects by name and DDL

In dbForge Studio for MySQL, you can find an object in a database using the DDL mode.

1. Navigate to Database > Find Object.

2. Click DDL and enter a search request into the field.

3. Optionally, click Search options to select an additional find option from the drop-down list. The available options are Match Case, Match Whole Word, and Use WildCards. By default, the Use WildCards option is selected.

4. You can change the scope of search results by specifying the search parameters, such as an object type, database, and connection to search in.

Object types. Select object types, in which the search will be performed. By default, all object types are selected.
Database. Select a database to search from a drop-down list. The database you are currently connected to is selected by default.
Connection. Select a connection for search from a drop-down list. The connection you are currently using is selected by default.

5. To start the search, click Start Search. During the search, you will see the button with a binocular and a progress spinner indicating that the search is in progress. You can stop the search at any point by clicking this button.

6. When the search is completed, the details of the search results will be displayed in the Search Results grid. When you select a result in the grid, the Preview window below the grid displays the DDL statements associated with the selected result.

Search a table and view data

Also, you can seek data in database tables in dbForge Studio for MySQL.