Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to improve full-text search efficiency in MySQL", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "How to improve the efficiency of full-text search in MySQL"!

Many Internet applications offer full-text search, where users can locate matching records using a word or phrase as a query term. Behind the scenes, these programs use LIKE statements in a SELECT query to execute such queries, which, while feasible, is an extremely inefficient method for full-text lookup, especially when dealing with large amounts of data.

MySQL provides a solution to this problem based on built-in full-text lookup. Here, developers simply mark the fields that need full-text lookup and then run searches on those fields using special MySQL methods, which not only improves performance and efficiency (because MySQL indexes those fields to optimize searches), but also enables higher quality searches because MySQL uses natural language to intelligently rank results to eliminate irrelevant items.

How to improve full-text search efficiency in MySQL

1. Set up basic tables

To start with creating the example table, use the following SQL command:

mysql>CREATETABLEreviews(idINT(5)

The above command creates a simple music album database (mostly whole paragraphs of text) and then adds a few records to the table:

mysql>INSERTINTO`reviews`(`id`,`data`)VALUES

Verify correct entry of data:

mysql>SELECT*FROMreviews;

2. Define full-text search fields

Next, define the fields you want to index as full-text search:

mysql>ALTERTABLEreviewsADDFULLTEXTINDEX(data);

Use the SHOWINDEXES command to check that the index has been added:

mysql>SHOWINDEXESFROMreviews;

| 2|

+----+

2rowsinset(0.00sec)

Here MATCH() compares the text in the field passed to it as an argument with the argument passed to AGAINST(), and if there is a match, it returns in the normal way. Note that you can pass more than one field to view with MATCH()-just use commas to separate the field list.

When MySQL receives a request for a full-text search, it internally scores each record; unmatched records score zero, while "more relevant" records receive relatively higher scores than "less relevant" records. Relevance is determined by MySQL's set of distinguishing criteria, see MySQL's user manual for more information.

To see how each record was scored, simply return the MATCH() method as part of the result set, as follows:

mysql>SELECTid,MATCH(data)AGAINST('rock')FROMreviews;

On MySQL to improve the efficiency of full-text search methods for everyone introduced here, I believe that through the above study, we now improve the efficiency of full-text search in MySQL to understand, I hope everyone can master, and then 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|+----+-------------------------------+3rowsinset(0.00sec)

+---------+---------------+--------+------+------------+---------+|Table |Column_name |Packed|Null|Index_type|Comment|----------+---------------+--------+------+------------+---------+|reviews| id |NULL | |BTREE ||| reviews| data|NULL |YES |FULLTEXT ||+---------+---------------+--------+------+------------+---------+2rowsinset(0.01sec)

QueryOK,3rowsaffected(0.21sec)Records:3 Duplicates:0 Warnings:0

+----+--------------------------------------------+|id|data |+----+--------------------------------------------+| 1|Gingerboyhasanewsingleoutcalled... || 2|Helloall,IreallylikethenewMadon...|| 3|HaveyouheardthenewbandHotterThan...|+----+--------------------------------------------+ 3rowsinset(0.00sec)

(1,'GingerboyhasanewsingleoutcalledThrowingRocks.It'sgreat! ');mysql>INSERTINTO`reviews`(`id`,`data`)VALUES(2,'Helloall,IreallylikethenewMadonnasingle.Oneofthehottesttrackscurrentlyplaying... I'vebeenlisteningtoitallday');mysql>INSERTINTO`reviews`(`id`,`data`)VALUES(3,'HaveyouheardthenewbandHotterThanHellTheyhavefivemembersandtheyburntheirinstrumentswhentheyplayinconcerts.Theseguystotallyrock! Like,awesome,dude! ');

PRIMARYKEYNOTNULLAUTO_INCREMENT,dataTEXT);

At this point, I believe that everyone has a deeper understanding of "how to improve the efficiency of full-text search in MySQL," so you may wish to actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.