Optimize Query Execution in Mysql
In the previous article we learned the concept and mechanism of operation of the B-tree, an indexing interface to make querying data faster, but that is not enough. In today’s article, we will learn a very important issue that is how to write the query properly and optimize processing speed. Perhaps in the process of working today we are often familiar with frameworks, such as Laravel. Writing your own query is a bit of an understatement because the framework already supports it, but if the project is large enough you will find it essential to understand queries in mysql. And if not handled wisely, it will kill a system in the future, because when the database data is large enough, our query will be problematic and must be optimized even when using pure laravel or sql. Let’s start.
Why is the query slow?
We need to know that queries are tasks and executing these tasks takes time. What we need to do to optimize a query is to make it simple with fewer tasks, which means that the execution time will be reduced, or to make them run faster.
Execution is one of the most important stages in the life cycle of a query. It involves lots of calls to get data from the storage engine, query in rows or simply post-query tasks like grouping or sorting. Naturally, these tasks will consume network and CPU resources. In some cases, executing too many times, too long or unnecessary queries will consume time, this is the goal we need to accomplish is to eliminate such queries to make time. Faster response. In this article we need to learn and answer some of the questions below.
Did you really get the data you needed?
Here are some cases of indiscriminate queries
Fetch more rows of data than necessary
This is a common mistake in most applications and most types of databases, which is to retrieve N records by command selectbut then only use a certain number of them. A very typical example is to get articles that meet certain conditions but only use the top 10 articles.
Fetch too many columns from the join statement between tables.
For a specific example, if you want to get all the information about the actors in the movie Mắt Biếc, don’t do this:
mysql> SELECT * FROM actors
-> INNER JOIN film_actors USING(actor_id)
-> INNER JOIN films USING(film_id)
-> WHERE films.title = 'Mat Biec';
Obviously we only need to retrieve the actor information but the above query will return all actors, file_actor, to films. Obviously too redundant. Please follow the way below
mysql> SELECT actors.* FROM actors...;
Fetch all columns
Much like the above case, this one is more typical because often we do everything select *rather than specifying the field to retrieve.
Fetch the same data multiple times
If we are not careful, getting the same data repeatedly will reduce the performance of the application. A common example is looping through an array of data and then fetching another data to display on the web.
Is Mysql checking too much data?
After making sure you get only the data you need, next you need to look to see if your query is checking too much data. There are 3 factors to evaluate
- Response time
- Number of rows checked
- Number of rows returned
Response time
Response time is an important factor to evaluate the performance level of mysql application. However, this factor is not always clear. Response time includes two components: processing time and standby time Thời gian xử lýis time actually that server handles queries to retrieve data Thời gian chờAs part of the processing time, but it is not processed immediately, but waiting for something, like an I / O operation to complete, or a row lock.
Row checked and row returned
Understanding the searched data and the returned data is helpful for us to know how the data we need to be searched, but this is not perfect data to know where a bad query is. , because shorter rows will give different results, or searching rows in memory will be much faster from disk.
Usually we always expect the number of rows to check will be equal to the number of results returned, but not always, when the data to get joins from multiple server tables must access each row in each table. to create a result row. The percentage of goods checked against returned items is usually from 1: 1 to 1: 10 and may be even larger.
How to optimize the query
When you need to optimize a problematic query, you need to find a different way to get the results you want but don’t necessarily return the same data set but you can convert the data into a type. the equivalent is more optimal, but we have to make sure that changing the query will bring more efficiency, the change data set will lead to the need to revise the web, but this is what is fight because if you don’t fix your query, your web will be slow or even die. Let’s see a few ways to do this.
What about a complex query compared to multiple queries?
I often have the hobby of combining multiple queries into one big query because I think that will increase the performance of the application but in fact whether it is. This is not really, because Mysql is designed to be able to connect and disconnect very well to handle small and very fast queries. Mysql can handle more than 100k queries per second so splitting a large query into many small queries is not really a problem. However, that is not why we let the application run multiple queries just to do a job that we could have done with a query that is not too complicated. Splitting large queries into multiple queries is only appropriate when the query is considered too complex within the developer understanding and the server processing speed is not too strong.
Can split Join into multiple queries
As an application to retrieve data from multiple tables, we often join tables together
mysql> SELECT * FROM tag
-> JOIN tag_post ON tag_post.tag_id=tag.id
-> JOIN post ON tag_post.post_id=post.id
-> WHERE tag.tag='mysql';
We can write more specifically like this
mysql> SELECT * FROM tag WHERE tag='mysql';
mysql> SELECT * FROM tag_post WHERE tag_id=1234;
mysql> SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);
However, this is only a specific condition for knowing in advance the parameters to be passed, but the above method can be considered as a generalization and is often chosen more often.
Conclude
Above are some knowledge about optimizing queries at a basic level, not really extensive, but can help you better understand the database design and the query appropriately and increase the performance of the application. use. Knowledge of the article is quite abstract because the author is also in the process of self-study, hoping you can understand. Thanks.
#query #javascript #mysql
