Teach you how to improve the efficiency of full-text search in MySQL

Many Internet applications provide full-text search capabilities, and users can use a word or phrase as a query item to locate matching records. In the background, these programs use the LIKE statement in a select query to execute such a query, although this method is feasible, but for Full-text lookup, this is an extremely inefficient approach, especially when dealing with large amounts of data.

MySQL provides a solution for this problem based on the built-in Full-text lookup. Here, developers simply mark out fields that require Full-text lookup, and then run the search in those fields using a special MySQL method, which not only improves performance and efficiency (because MySQL indexes these fields to optimize the search), but also enables higher quality searches, Because MySQL uses natural language to intelligently rank results to get rid of irrelevant items.

1. Set the basic form

Starting with the example table, use the following SQL command:

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 the entire paragraph text), 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 the correct data entry:

　　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 that you want to index 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

Use the show indexes command to check that 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　 |　　　　 |
　　+---------+---------------+--------+------+------------+---------+
　　2 rows in set (0.01 sec)

| 2 |

+----+

2 rows in Set (0.00 sec)

Here, Match () compares the text in the field passed as a parameter to the argument passed to against (), and if there is a match, returns in the normal way. Note You can pass more than one field to view with match ()-Just separate the field list with commas.

When MySQL receives a request for a full-text search, it scores each record internally, with unmatched records scoring zero, and more relevant records getting a higher score than "less relevant" records. Dependencies are determined by a series of MySQL criteria, and you can get more information by looking at MySQL's user manual.

To see how each record is scored, just return to the match () method as part of the result set, as follows:

　　mysql> SELECT id, MATCH (data) AGAINST ('rock') FROM reviews;
　　+----+-------------------------------+
　　| id | MATCH (data) AGAINST ('rock') |
　　+----+-------------------------------+
　　|　1 |　　　　　　　　　　　　　　 0 |
　　|　2 |　　　　　　　　　　　　　　 0 |
　　|　3 |　　　　　　　 1.3862514533815 |
　　+----+-------------------------------+
　　3 rows in set (0.00 sec)