How to Improve full-text search efficiency in MySQL

MySQL databaseAs we all know, full-text search is pursuing high efficiency. Today's society is developing fast, and many things require high efficiency. MySQL Databases keep up with the pace of the times, speeding up the pace, and improving the efficiency of full-text search, next we will teach you how to improve the full-text search efficiency in MySQL.

Many Internet applications provide full-text search functions. Users 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.

1. Set the basic table

Use the following SQL command to create an example table:

mysql> CREATE TABLE reviews (id INT(5)

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

Verify that the data is entered correctly:

mysql> SELECT * FROM reviews;

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);

Run the show indexes command to check whether the index has been added:

mysql> SHOW INDEXES FROM reviews;

| 2 |

+ ---- +

2 rows in set (0.00 sec)

Here, MATCH () compares the text in the field passed to it as a parameter with the parameter passed to AGAINST (). If there is a MATCH, it is returned in the normal way. Note: You can pass more than one field and use MATCH () to view the field list. You only need to use commas to separate the field list.

When MySQL receives a full-text search request, it scores each record internally, and the non-matching record score is zero, the "more relevant" record will get a relatively higher score than the "less relevant" record. Relevance is determined by a series of MySQL differentiation standards. You can view the MySQL user manual to obtain more information.

To view the score of each record, you only need to return the MATCH () method as part of the result set, as shown below:

mysql> SELECT id, MATCH (data) AGAINST ('rock') FROM reviews;

The Method for Improving the full-text search efficiency in MySQL is introduced here. I believe that through the above learning, you are now familiar with improving the full-text search efficiency in MySQL, I hope everyone can master it and apply it to future work. I believe it will bring a lot of convenience to everyone's work.

+ ---- + --------------------------------- +

| Id | MATCH (data) AGAINST ('Rock ') |

+ ---- + --------------------------------- +

| 1 | 0 |

| 2 | 0 |

| 3 | 1.3862514533815 |

+ ---- + --------------------------------- +

3 rows in set (0.00 sec)

+ --------- + --------------- + -------- + ------ + ------------ + --------- +

| Table | Column_name | Packed | Null | Index_type | Comment |

---------- + --------------- + -------- + ------ + ------------ + --------- +

| Reviews | id | NULL | BTREE |

| Reviews | data | NULL | YES | FULLTEXT |

+ --------- + --------------- + -------- + ------ + ------------ + --------- +

2 rows in set (0.01 sec)

Query OK, 3 rows affected (0.21 sec)

Records: 3 Duplicates: 0 Warnings: 0

+ ---- + -------------------------------------------- +

| 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)

(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! ');

Primary key not null AUTO_INCREMENT, data TEXT );