Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "es search optimization and mysql query optimization". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

This week has optimized the search function of our Shazhuo official website. At present, this search is based on the video title. Previously, we did a word segmentation search for the keywords entered by the user, such as user input [robot]. As long as any word about the key word in the video title will be found, such as the title containing the word "person" will be displayed, and may be very high in the list. So I optimized the current search, now the search is to give priority to adjacent phrase query match_phrase, if the adjacent phrase query results are less than 2, then carry out a general word segmentation query, this adjacent phrase query requires that all query items in the request string must exist in the document, the order of the text must also be consistent with the request string, and connected to each other. In this case, the result of [robot] in the title must be at the top of the list.

The modified query body is as follows:

{"query": {"bool": {"filter": {"match_phrase": {"video_title": word} "must": {"range": {"video_date": {"gte": "now-30d+8h", "lte": "now+8h"}} "sort": {"video_like": {"order": "desc"}}, "from": start, "size": size, "highlight": {"pre_tags": ["]," post_tags ": ["] "fields": {"video_title": {

}

During this week, I also optimized our data table query by adding a common index and a federated index. Now the amount of data in our video data table is hundreds of megabytes. When querying according to the video industry, the mysql response speed is about 600ms. I added a general index to the industry field, and the query response is about 30 ms. The optimization result is still obvious before and after. We also have the ability to filter according to video classification. Video release time and video classification fields are often used in the query conditions of video tables, so I added a joint index to these two. After adding the federated index, the query speed is also about 30ms. As netizens said, if the index mysql is properly designed and used, it is a Lamborghini, otherwise it is a human tricycle.

Add the index statement as follows:

1. General index ALTER TABLE `aweme_ data` ADD INDEX category_id_index (`index id`); 2. Add a federated index ALTER TABLE `aweme_ data` ADD INDEX type_create_time (`type_ id`, `aweme_create_ time`)

This is the end of "es search Optimization and mysql query Optimization". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.