One step ahead in mysql search: full-text search

Full Text Search is searching a string among multiple coulmns of a table to fetch the more relevant records.

In this blog we will demostrate how to implement full text search to fetch relavent responses.

There are few simple steps to implement full text search in mysql-

1. Create table with full text schema

[java]
Create Table Video(
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
description TEXT,
FULLTEXT (title,description)
);
[/java]

2. Populate some data.

[java]
INSERT INTO Video (title,description) VALUES
(‘MySql Video Tutorial’,’Mysql is sql database’),
(‘Mongo Video Tutorial’,’Mongo is no sql database’);

[/java]

You can populate the more data in same way.

3. Search with query string – ‘sql’

[java]
SELECT * FROM Video
WHERE MATCH (title,description) AGAINST (‘sql’);

Result is:

+—-+———————-+————————–+
| id | title | description |
+—-+———————-+————————–+
| 1 | MySql Video Tutorial | Mysql is sql database |
| 2 | Mongo Video Tutorial | Mongo is no sql database |
+—-+———————-+————————–+
2 rows in set (0.03 sec)

[/java]

See the result, String ‘sql’ is only in description, not in title.

If there is table already created on production databse, we can alter the existing table to for full text search by the query:

[java]
alter table Video add FULLTEXT(title , description );
[/java]

Relevant search results help to engage users with the site and keep your audience sticky.
More number of search results help to serve more content for target audience.

Mysql is supporting full text search from version MySQL 5.6 onwards, before this it supports only in MyISAM Engine.

Mysql supports different type of search;
1 Boolean full text search
2 Natural search language

Both sql(My-Sql) and no-sql(ElasticSearch) are implementing this concept for providing efficient searching solutions.

Hope this helps.