Essential SQL query optimization techniques to speed up Website access and SQL access

Essential SQL query optimization techniques to speed up Website access and SQL access

In this article, I will introduce how to identify queries that cause performance problems, how to locate their problems, and how to quickly fix these problems and other methods to speed up the query.

You must know that a website with quick access can be liked by users, help websites increase rankings from Google, and help websites increase conversion rates. If you have read articles on website performance optimization, such as setting the best server implementation, killing slow code, and loading Images Using CDN, you think your WordPress website is fast enough. But is that true?

If you use a dynamic database-driven website, such as WordPress, you may still have a problem to solve: database query slows down Website access.

In this article, I will introduce how to identify queries that cause performance problems, how to locate their problems, and how to quickly fix these problems and other methods to speed up the query. I will take the slow query speed of the portal website deliciousbrains.com as an actual case.

Positioning

The first step in processing slow SQL queries is to find slow queries. Ashley has praised the debugging plug-in Query Monitor in his previous blog. The database Query feature of this plug-in makes it a valuable tool for locating slow SQL queries.

The plug-in reports database requests during all page requests, and can filter these queries by calling the query code or the original (plug-in, topic, and WordPress core), highlighting duplicate queries and slow queries.

If you do not want to install a debugging plug-in the production environment (performance overhead reason), you can also open the MySQL Slow Query Log so that all queries executed at a specific time will be recorded. This method is relatively easy to configure and set the storage query location.

Because this is a service-level adjustment, the performance impact will be less than the use of debugging plug-ins, but should also be disabled when not used.

Understanding

Once you find a query that you need to find at a large cost, the next step is to try to understand it and find out what slows down the query. Recently, when we developed our website, we found an 8-second query.

We use WooCommerce and customized WooCommerce software plug-ins to run our plug-in store. The purpose of this query is to obtain all the subscriptions of customers who know the customer ID.

WooCommerce is a slightly complex data model. Even if the order is stored in a custom type, the user ID (WordPress created by the store for each user) is not stored in post_author, it is part of the later data. The subscription software plug-in creates a link for the custom table. Let's have a better understanding of the query information.

Treat MySQL as a friend

MySQL has a convenient statement DESCRIBE, which can output table structure information, such as field names and data types. Therefore, when you execute DESCRIBE wp_postmeta; you will see the following results:

You may already know this statement. But do you know that the DESCRIBE statement can be used before SELECT, INSERT, UPDATE, REPLACE, and DELETE statements? It is even more familiar with its synonym "EXPLAIN" and provides detailed information about how the statement is executed.

Here is the query result:

At first glance, this is hard to explain. Fortunately, people use SitePoint to summarize a comprehensive guide to understanding statements.

The most important field is type, which describes how a table is made up.

If you want to read all the content, it means that MySQL needs to read the entire table from the memory, increase the I/O speed and load it on the CPU. This is called "full table browsing"-this will be detailed later.

The rows field is also a good identifier that identifies what MySQL will have to do. It shows how many rows are searched in the result.

Explain also gives us a lot of information that can be optimized. For example, the pm2 table (wp_postmeta) tells us that it is Using filesort, because we use the order by statement to sort the results. If we want to group the query results, this will increase the overhead for execution.

Visualization Research

For this type of research, MySQL Workbench is another convenient and free tool. Open the database with MySQL5.6 or a later version. The EXPLAIN results can be output in JSON format. At the same time, MySQL Workbench converts the JSON into a visualized execution statement:

It automatically reminds users to pay attention to the query problems in colors. We can see that the tables connected to wp_woocommerce_software_licences (alias l) have serious problems.

Solution

You should avoid (https://dev.mysql.com/doc/refman/5.7/en/table-scan-avoidance.html) this kind of all table browsing queries, because he uses the non-index field order_id to connect the wp_woocommerce_software_licences table and wp_posts table. This is a common and easy-to-Solve Problem for slow queries.

Index

Order_id is a very important landmark data in the table. If you want to query it in this way, you need to create an index on the column. In addition, MySQL will scan each row of the table by word, until we find the desired line. Let's add an index and see how it works:

Wow, what a wonderful job! We successfully added indexes and reduced the query time by 5 s.

Understand your query statements

Check the query statement -- check every join and every subquery. Have they done what they shouldn't do? What optimization can be done here?

In this example, we connect the licenses table and posts Table through order_id and set post type to shop_order. This is to ensure that we only use the correct order records by maintaining data integrity, but in fact this is redundant in the query.

We know that this is a security bet. In the posts Table, the software license line is associated with the WooCommerce order through order_id, which is mandatory in the PHP plug-in code. Let's remove join to see if there is any improvement:

 

The increase is not very big, but now the query time is less than 3 seconds.

Cache all data

If your server does not use the MySQL query cache by default, you should enable the cache.

Enabling cache means that MySQL will save the execution results of all statements and statements. If a statement identical to that in the cache needs to be executed, MySQL will return the cached results. The cache will not expire, because MySQL will refresh the cache after the table data is updated.

The query Monitor found that when loading a page, our query statement was executed four times. Although MySQL query cache is good, repeated reading of database data in a request should be completely avoided.

Static cache in your PHP code is very simple and can solve this problem very efficiently. Basically, when the first request is made, the query results are obtained from the database and stored in the static attributes of the class. Then, the subsequent query statement calls will return results from the static attributes:

The cache has a lifecycle. Specifically, an instantiated object has a lifecycle. If you are viewing cross-request query results, you need to implement a Persistent Object cache. However, your code should be responsible for setting the cache, and make the cache invalid when the basic data changes.

Empathy

It is not only about adjusting queries or adding indexes, but also about other ways to speed up query execution. The slowest part of our query is from the customer ID to the product ID and then to the work done by joining the table. We must do this for each customer.

Can we capture customer data as needed? If so, we only need to add it once.

You can create a data table to store license data, along with all license user IDs and product identifiers to process non-standardized (denormalized) data and query specific customers.

You need to use the MySQL trigger on INSERT/UPDATE/DELETE to recreate the table (but this depends on the table to be changed by data), which significantly improves the performance of data query.

Similarly, if some connections slow down the query speed in MySQL, it may be faster to split the query into two or more statements and execute them separately in PHP, then, you can collect and filter the results in the code. Laravel has done something similar through pre-loading in Eloquent.

If you have a large amount of data and many different custom post types, WordPress may slow down the query speed on the wp_posts table. If you find that the type of the queried post is slow, you can consider moving from the storage model of the custom post type to the custom table-more information will be introduced in the following article.

Conclusion

Through these query optimization methods, we try to reduce the number of queries from 8 seconds to 2 seconds, and reduce the number of queries from 4 times to 1. It should be noted that these query times are recorded during running in our development environment, and the production environment is faster.

This is a useful guide for tracking slow queries and fixing issues. Optimizing the query may look like a terrible task, but as long as you try and win some preliminary victories, you will start to find errors and hope to make further improvements.