How to Perform Full-Text Search in MySQL Database

Performing a full-text search in MySQL allows you to search for words or phrases within text columns efficiently. Here are the steps to set up and perform a full-text search:

1. Create a Full-Text Index:

To perform a full-text search, you need to create a full-text index on the columns you want to search. This index enables MySQL to efficiently search for words within the specified columns.

ALTER TABLE your_table
ADD FULLTEXT (column1, column2, ...);

Replace your_table with the actual name of your table and column1, column2, etc., with the columns you want to index for full-text search.

2. Execute a Full-Text Search Query:

Once you have created the full-text index, you can execute a full-text search query using the MATCH() and AGAINST() functions.

SELECT * FROM your_table
WHERE MATCH(column1, column2) AGAINST('search_term');

Replace your_table, column1, column2, and search_term with your actual table, columns, and search term.

3. Full-Text Search Modifiers:

MySQL provides several modifiers to customize the behavior of the full-text search:

• IN BOOLEAN MODE: Allows you to use boolean operators (+, -, *, "", <>) in your search query.

SELECT * FROM your_table
WHERE MATCH(column1, column2) AGAINST('search_term' IN BOOLEAN MODE);

• WITH QUERY EXPANSION: Provides more results by including words similar to the search term.

SELECT * FROM your_table
WHERE MATCH(column1, column2) AGAINST('search_term' WITH QUERY EXPANSION);

Important Notes:

• The columns you include in the MATCH function should be part of the full-text index.
• The full-text index only applies to specific storage engines (e.g., MyISAM, InnoDB, etc.).
• Short or common words (stopwords) may not be indexed. You can customize the stopword list.
• The relevance of results is determined by factors like word frequency and document length.

Example:

Let’s assume you have a table called articles with columns title and content. You want to perform a full-text search for the term ‘MySQL’. Here’s an example query:

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL');

This query will return all rows from the articles table where either the title or content contains the word ‘MySQL’.