Benny Yang
Posted on January 24, 2024
Recently I've been hooking up with Mysql full-text search, which is a compromised solution for building up a simple keyword search feature instead of utilizing external search engines such as ElasticSearch.
MySQL full-text search
MySQL supports for full-text searching, by creating an index with type FULLTEXT, after then, we can perform full-text search upon certain fields using MATCH(column) AGAINST (string) syntax.
Difference with LIKE
When it comes to fuzzy searching in MySQL, the first approach most people will come up with is applying LIKE operator.
SELECT * FROM wide_table WHERE column_A LIKE '%keyword%'
However, as the dataset grows, it could be resource-intensive when doing this, especially it will perform a full table scan when it comes to searching VARCHAR, char and TEXT field with a wildcard character % at the beginning.
In contrast, full-text searches are quicker in this case since it was indexed in advance and it provides additional features such as search with operators, data relevance score... etc.
Search Modes
MySQL does offer multiple search modes while performing full-text search by specifying it at the end of the query
MATCH (column) AGAINST (string) IN NATURAL LANGUAGE / BOOLEAN MODE
Natural Language Mode
Natural Language Mode is the default mode for full-text searches in MySQL. It looks for occurrences of the search terms within the indexed columns and calculates relevance based on factors like term frequency and proximity.
Let's dive into some examples
// Create table with full-text index
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title, body)
) ENGINE=InnoDB;
// Search with "smartphone features"
SELECT * from articles where MATCH(title, body) AGAINST('smartphone features' IN NATURAL LANGUAGE MODE);
And we got the result with
| id | title | body |
| --- | --------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| 1 | Smartphone Evolution | The evolution of mobile devices over the years has been significant. Early models were primarily for voice communication, but modern iterations offer a plethora of features from internet connectivity to multimedia support. |
| 3 | Technological Advancements in Cellular Phones | Cellular phones have undergone tremendous technological advancements. The latest models boast features like facial recognition, augmented reality capabilities, and voice-activated assistants. |
| 4 | The Impact of Mobile Phones on Daily Life | Mobile phones have drastically impacted our daily lives. They keep us aconnected, provide instant access to information, and offer an array of features like GPS navigation, digital wallets, and health monitoring apps. |
Getting relevancy score from each row
SELECT *, MATCH(title, body) AGAINST('smartphone features' IN NATURAL LANGUAGE MODE) as score from articles;
| id | title | body | score |
| --- | --------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------- |
| 1 | Smartphone Evolution | The evolution of mobile devices over the years has been significant. Early models were primarily for voice communication, but modern iterations offer a plethora of features from internet connectivity to multimedia support. | 0.3780859112739563 |
| 2 | The Rise of Smart Devices | Smart devices, especially in the realm of telecommunication, have become ubiquitous. These devices, often fitting in the palm of our hand, offer functionalities ranging from high-quality video calls to seamless social media access. | 0 |
| 3 | Technological Advancements in Cellular Phones | Cellular phones have undergone tremendous technological advancements. The latest models boast features like facial recognition, augmented reality capabilities, and voice-activated assistants. | 0.015609688125550747 |
| 4 | The Impact of Mobile Phones on Daily Life | Mobile phones have drastically impacted our daily lives. They keep us aconnected, provide instant access to information, and offer an array of features like GPS navigation, digital wallets, and health monitoring apps. | 0.015609688125550747 |
As we saw from the example above, the result was automatically ordered by it's relevancy score and we can also retrieve the exact score by specifying it as the selected column.
The score is computed based on the number of words in the row (document), the number of unique words in the row, the total number of words in the collection, and the number of rows that contain a particular word.
Boolean Mode
Boolean Mode provides greater control and precision through the use of boolean operators like + (must include), - (exclude), and others. This allows for complex and specific search queries.
For example,
SELECT title, body
FROM articles
WHERE MATCH(title, body) AGAINST('+smartphone features' IN BOOLEAN MODE);
We got
| title | body |
| -------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Smartphone Evolution | The evolution of mobile devices over the years has been significant. Early models were primarily for voice communication, but modern iterations offer a plethora of features from internet connectivity to multimedia support. |
by specifying "smartphone" as a must included string.
By utilizing these powerful operators, this mode is particularly useful when you need precise control over the search criteria, especially for complex queries or when filtering out specific terms is important.
Search with Query Expansion
Query Expansion can sometimes capture a broader range of related content, potentially addressing some aspects of context.
It works by performing the search twice, where the search phrase for the second search is the original search phrase concatenated with the few most highly relevant documents from the first search.
Let's test with previous example
SELECT *, MATCH(title, body) AGAINST('smartphone features' WITH QUERY EXPANSION) as score FROM articles WHERE MATCH(title, body) AGAINST('smartphone features' WITH QUERY EXPANSION);
| id | title | body | score |
| --- | --------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------ |
| 4 | The Impact of Mobile Phones on Daily Life | Mobile phones have drastically impacted our daily lives. They keep us connected, provide instant access to information, and offer an array of features like GPS navigation, digital wallets, and health monitoring apps. | 8.474403381347656 |
| 1 | Smartphone Evolution | The evolution of mobile devices over the years has been significant. Early models were primarily for voice communication, but modern iterations offer a plethora of features from internet connectivity to multimedia support. | 7.643221378326416 |
| 3 | Technological Advancements in Cellular Phones | Cellular phones have undergone tremendous technological advancements. The latest models boast features like facial recognition, augmented reality capabilities, and voice-activated assistants. | 6.737030029296875 |
| 2 | The Rise of Smart Devices | Smart devices, especially in the realm of telecommunication, have become ubiquitous. These devices, often fitting in the palm of our hand, offer functionalities ranging from high-quality video calls to seamless social media access. | 0.4843146502971649 |
In this case, the relevance order of each row was changed compared to previous results we got since it searches again for the relevant concepts, the exact phrase doesn't appear in the fourth row but it was returned since it's content is related to the keyword.
Customization
Let's say we want to build a customize keyword search by prioritizing on certain fields. We could achieve this by putting weight upon their relevancy score using SELECT, for instance :
SELECT *,
(MATCH(title) AGAINST('smartphone features' IN NATURAL LANGUAGE MODE) * 5) +
(MATCH(body) AGAINST ('smartphone features' IN NATURAL LANGUAGE MODE) * 3) as score
FROM articles ORDER by score;
In this case, if two rows of data have there body and title matching respectively, the row with matched title will be populated first.
Conclusion
In conclusion, MySQL full-text search provides a straightforward and accessible solution for keyword search functionality, presenting a simpler alternative to more complex systems like Elasticsearch in terms of less time-consuming to build and configure, it also offers clear advantages over the basic LIKE operator. Full-text search benefits from FULLTEXT indexing, enabling faster and more efficient searches. It supports diverse search modes such as Natural Language and Boolean Mode, but lacks Elasticsearch's advanced natural language processing and scalability. Although beneficial for basic to moderate search requirements. Furthermore, it can be challenging to fine-tune for specific needs, making it less suitable for highly complex or large-scale search scenarios.
References
Posted on January 24, 2024
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.