Get better search results in MySQL _ MySQL

Many Internet applications provide full-text search functions to get better search results in MySQL. you can use a word or word segment as a query item to locate matching records. In the background, these programs use the LIKE statement in a SELECT query to execute this query. Although this method is feasible, it is an extremely inefficient method for full-text search, especially when processing a large amount of data.

MySQL provides a built-in full-text search solution for this problem. Here, developers only need to simply mark the fields that require full-text search, and then use special MySQL methods to run search on those fields, this not only improves the performance and efficiency (because MySQL indexes these fields to optimize the search), but also achieves higher quality search, because MySQL uses natural language to intelligently rate the results to remove irrelevant items.

This article will show you how to perform full-text search in MySQL.

1. set the basic table

Use the following SQL command to create an example table:

Mysql> create table reviews (id INT (5) primary key not null AUTO_INCREMENT, data TEXT );
The above Command creates a simple music album database (mainly text in the entire segment), and then adds some records to the table:

Mysql> insert into 'Reviews '('id', 'data') VALUES
(1, 'gingerboy has a new single out called Throwing Rocks. It's great! ');
Mysql> insert into 'Reviews '('id', 'data') VALUES
(2, 'Hello all, I really like the new Madonna single.
One of the hottest tracks currently playing... I 've been listening to it all day ');
Mysql> insert into 'Reviews '('id', 'data ')
VALUES (3, 'Have you heard the new band Hotter Than Hell?
They have five members and they burn their instruments when they play in concerts.
These guys totally rock! Like, awesome, dude! ');
Verify that the data is entered correctly:

Mysql> SELECT * FROM reviews;
+ ---- + -------------------------------------------- +
| Id | data |
+ ---- + -------------------------------------------- +
| 1 | Gingerboy has a new single out called... |
| 2 | Hello all, I really like the new Madon... |
| 3 | Have you heard the new band Hotter Than... |
+ ---- + -------------------------------------------- +
3 rows in set (0.00 sec)
2. define full-text search fields

Next, define the fields to be indexed as full-text search

Mysql> alter table reviews add fulltext index (data );
Query OK, 3 rows affected (0.21 sec)
Records: 3 Duplicates: 0 Warnings: 0
Run the show indexes command to check whether the index has been added:

Mysql> show indexes from reviews;
+ --------- + --------------- + -------- + ------ + ------------ + --------- +
| Table | Column_name | Packed | Null | Index_type | Comment |
---------- + --------------- + -------- + ------ + ------------ + --------- +
| Reviews | id | NULL | BTREE |
| Reviews | data | NULL | YES | FULLTEXT |
+ --------- + --------------- + -------- + ------ + ------------ + --------- +