Reading Time: 2 minutes

Full-text search in databases allows you to perform advanced searches on the content of text-based fields, such as large blocks of text, articles, or documents. It goes beyond simple keyword matching and enables you to search for words or phrases within the text, taking into account relevance, word proximity, and ranking.

With full-text search, you can find relevant information even if the exact search terms are not present.

By using full-text search, you can build powerful search functionalities within your database applications, making it easier for users to find desired information quickly and effectively. It is commonly used in content management systems, e-commerce platforms, document repositories, and knowledge bases.

To perform a full-text search in MySQL, you can use the MATCH() function in combination with the AGAINST() operator. This allows you to search for specific keywords or phrases across one or more columns defined as full-text indexes. The results can be filtered and sorted based on the relevance score provided by the MATCH() function.

Searching related data in Mysql is pretty easy, let’s see how the syntax looks like

SELECT * 
FROM my_table 
WHERE MATCH(col1, col2) AGAINST('my super awesome text' IN NATURAL LANGUAGE MODE)

Example of full text search from scratch:

Create a table

CREATE TABLE tutorial (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 
title VARCHAR(200), 
description TEXT, 
FULLTEXT(title,description)
) ENGINE=InnoDB;

Insert some data

INSERT INTO tutorial (title,description) VALUES
('SQL Joins','An SQL JOIN clause combines rows from two or more tables. It creates a set of rows in a temporary table.'),
('SQL Equi Join','SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality.'),
('SQL Left Join','The SQL LEFT JOIN, joins two tables and fetches rows based on a condition, which is matching in both the tables and the unmatched rows will also be available from the table before the JOIN clause.'),
('SQL Cross Join','The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN.'),
('SQL Full Outer Join','In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause.'),
('SQL Self Join','A self join is a join in which a table is joined with itself (which is also called Unary relationships), especially when the table has a FOREIGN KEY which references its own PRIMARY KEY.');
    
    

Search some records

SELECT * 
FROM tutorial 
WHERE MATCH(title,description) AGAINST ('left right' IN NATURAL LANGUAGE MODE);

Search some records with score

SELECT id, MATCH(title,description) AGAINST ('left right' IN NATURAL LANGUAGE MODE) AS score 
FROM tutorial;