August 7, 2023

Efficient Full Text Search in MySQL: Boosting Search Performance

The best time to establish protocols with your clients is when you onboard them.


Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.

Understanding Full-Text Search

Full-text search is a sophisticated approach that goes beyond simple keyword matching. It leverages techniques such as tokenization, stemming, query parsing, and ranking to provide accurate and relevant search results. By indexing the textual content and employing intelligent algorithms, full-text search systems enable users to extract meaningful information from large volumes of text-based data. Its architecture, and the key components that make it a valuable tool for efficient information retrieval.

Architecture of Full-Text Search

Data Ingestion: The first step in the full-text search process is data ingestion. Textual data from various sources, such as documents, articles, web pages, or database records, is collected and prepared for indexing. This involves cleaning the data, removing stop words, and transforming it into a suitable format for further processing.

Tokenization and Normalization: Once the data is ingested, it undergoes tokenization, where it is divided into smaller units called tokens. Tokenization helps in breaking down the text into meaningful segments, such as words, phrases, or other linguistic units

Indexing: It involves creating an index structure that maps each token to the corresponding documents where it appears. Various data structures, such as inverted indexes, are used to efficiently store and retrieve this information. The index enables quick lookup of documents based on the presence of specific terms or patterns, significantly speeding up the search process.

Query Processing: When a user submits a search query, the full-text search system parses and processes the query to identify the search terms, operators, and parameters. This includes tokenizing the query, applying stemming or normalization, and structuring the query for efficient search operations. Advanced search features like boolean operators, phrase searching, wildcard matching, and fuzzy matching enhance the flexibility and precision of the search queries.

Query Pattern in MySQL:

Indexing: To enable Full-Text Search, you need to create a full-text index on the specific column(s) you want to search within. The index is created using the FULLTEXT index type, which analyzes the text and creates an index structure that enables efficient searching.

Create Table your_table (id INT PRIMARY KEY, column_name TEXT, FULLTEXT (column_name));

Search Queries: Full-Text Search provides powerful search capabilities through the MATCH() AGAINST() syntax. You can construct search queries using Boolean operators (AND, OR, NOT) to combine multiple search terms.

Natural Language mode is the default mode and this mode performs linguistic analysis on the text and uses natural language processing techniques to enhance the search results. It takes into account factors like word relevance and performs stemming to match variations of words.

SELECT * FROM your_table WHERE MATCH(column_name) AGAINST ('search_value' IN NATURAL LANGUAGE MODE);

Boolean Mode: It allows you to combine search terms using AND, OR, and NOT operators to create complex queries. It supports grouping, search term modifiers, and relevance ranking for more precise and customizable searches. “+” and “-” symbols are used as search term modifiers.

“+” (plus sign): By placing a plus sign before a search term, you indicate that the term must be present in the records for them to be considered a match. For example, a query like ‘apple +juice’ will retrieve records that contain both “apple” and “juice”.

“-” (minus sign): When you prepend a search term with a minus sign, you exclude records that contain that term. For instance, a query like ‘apple -juice’ will retrieve records that contain “apple” but exclude those that also contain “juice”.

SELECT * FROM your_table WHERE MATCH(column_name) AGAINST ('+apple -juice' IN BOOLEAN MODE);

Ranking and Relevance: To present the most relevant results to the user, full-text search systems employ ranking algorithms. These algorithms assign a relevance score to each document based on factors like term frequency — Inverse Document frequency. The search results are then ordered based on their relevance scores, with the most relevant documents appearing at the top. This ranking mechanism ensures that users can quickly find the information they are seeking. Let us see how TF-IDF works.

TF-IDF is based on two main factors: term frequency (TF) and inverse document frequency (IDF):

Term Frequency (TF): Term frequency measures the number of times a term appears in a document. It represents the local importance of a term within a document. A higher term frequency indicates that the term is more relevant to the document.

Inverse Document Frequency (IDF): Inverse document frequency measures the global importance of a term by considering how commonly it occurs across the corpus . It is calculated by taking the logarithm of the ratio of the total number of documents to the number of documents containing the term.
IDF assigns a higher weight to terms that appear less frequently in the document collection, as they are considered more informative or discriminative. Terms that appear in many documents are given a lower IDF score, as they may be less significant for distinguishing or characterizing individual documents.

The TF-IDF score for a term in a document is calculated by multiplying the term’s TF by its IDF. This results in a higher score for terms that have high frequency within a document but are relatively rare across the entire document collection. Let’s take a simple example to illustrate how TF-IDF works:

Consider a document collection consisting of three documents:

Document 1: “The cat chased the mouse.”

Document 2: “The cat called another cat for help”

We want to calculate the TF-IDF scores for the term “cat” in each document. Here’s how it can be done:

Term Frequency (TF):

Document 1: TF(“cat”) = ⅕ = 0.2

Document 2: TF(“cat”) = 2/7 = 0.28

Inverse Document Frequency (IDF):

IDF(“cat”) = log(2/2) = 0

TF-IDF Calculation:

Document 1: TF-IDF(“cat”) = 0.2 * 0 = 0

Document 2: TF-IDF(“cat”) = 0.28 * 0 = 0

Using TF-IDF, the word “cat” is equally relevant for both the documents. So both documents will show up.

In a larger document collection, TF-IDF helps distinguish terms that are more specific to a particular document and have higher significance in characterizing its content.

Result Presentation: Once the search results are determined, they are presented to the user in a clear and intuitive format. This may include displaying snippets of the matched text, highlighting the matching terms, or providing additional metadata about the documents. The goal is to make it easy for users to identify and access the relevant information from the search results.

Query format to show the TF-IDF scores of the search results

SELECT *, MATCH (column_name) AGAINST ('search_term' IN BOOLEAN MODE) AS scoreFROM your_table ORDER BY score DESC;

In the query result, the score column will contain the TF-IDF calculated score.

Continuous Indexing and Updates: Full-text search systems often operate on dynamic data sources, where new documents are added, and existing documents are updated or removed. To ensure the search results remain up to date, continuous indexing processes are implemented. These processes monitor the data sources for changes and update the index accordingly, maintaining the accuracy and freshness of the search results.

Scalability and Distributed Architecture: For handling large-scale textual data, full-text search systems employ scalable and distributed architectures. By distributing the indexing and search processes across multiple nodes or machines, these systems can handle high volumes of data and serve search requests efficiently. Distributed architectures also provide fault tolerance and redundancy, ensuring uninterrupted search operations even in the presence of failures.


Full-text search is a powerful tool for efficiently retrieving relevant information from large text collections. Its architecture involves data ingestion, tokenization, indexing, query processing, ranking, and result presentation. These components enable users to navigate extensive textual content, addressing the challenge of information overload in the digital age.

August 7, 2023
min read
Subscribe to our newsletter
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Share this article

More articles