How to Optimize SQL Queries for Faster Sites

Image Optimization and Database Query Optimization: A Practical Guide to Improve the Speed ​​of WordPress Websites

This article was originally published on the Delicious Brains blog and is reproduced here with permission

You know that a fast website means happier users, better Google rankings and higher conversion rates. You might even think your WordPress site is fast enough: you've checked site performance, from best server setup practices to slow code troubleshooting, and offloading images to CDN, but is that just all?

For dynamic, database-driven websites like WordPress, you may still face a problem: database queries cause website speed to slow down.

In this post, I will walk you through how to identify queries that cause bottlenecks, how to understand the problems with these queries, and other ways to quickly fix and speed up. I'll use an actual query we recently solved that slows down the deliciousbrains.com customer portal.

Query and identification

The first step in fixing slow SQL queries is to find them. Ashley previously praised the Query Monitor debug plugin in his blog, and the database query capability of the plugin makes it a valuable tool for identifying slow SQL queries. This plugin reports all database queries performed during page requests. It allows you to filter them by the code or component (plugin, theme, or WordPress core) that calls them and highlight duplicate and slow queries:

If you don't want to install debug plugins on your production site (maybe you're worried about adding some performance overhead), you can choose to enable MySQL slow query logs, which logs all queries that take some time to execute. This is relatively easy to configure and set the logging location for the query. Since this is a server-level tuning, the performance impact will be less than the debug plugin on the site, but it should be turned off when not in use.

Understanding query questions

After finding the expensive query to improve, the next step is to try to understand what causes the query to slow down. Recently, when developing our website, we found that a query takes about 8 seconds to execute!

SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date

We use WooCommerce and WooCommerce software to subscribe to a custom version of the plugin to run our plugin store. The purpose of this query is to obtain all subscriptions to customers who we know the customer number. WooCommerce has a rather complex data model, even if the order is stored as a custom post type, the customer's ID (the store for which each customer creates WordPress user) is not stored as a post_author, but as part of the post metadata. The software subscription plugin also creates several custom table connections. Let's take a deeper look at the query.

Use MySQL tools

MySQL provides a convenient DESCRIBE statement that can be used to output information about the structure of a table, such as its columns, data types, and default values. So if you do DESCRIBE wp_postmeta;, you will see the following results:

Field	Type	Null	Key	Default	Extra
meta_id	bigint(20) unsigned	NO	PRI	NULL	auto_increment
post_id	bigint(20) unsigned	NO	MUL	0
meta_key	varchar(255)	YES	MUL	NULL
meta_value	longtext	YES		NULL

This is cool, but you probably already know. But did you know that the DESCRIBE statement prefix can actually be used for SELECT, INSERT, UPDATE, REPLACE, DELETE, and EXPLAIN statements? This is more often referred to as its synonym

, which will give us detailed information on how statements are executed.

The following are the results of our slow query:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	pm2	ref	meta_key	meta_key	576	const	28	Using where; Using temporary; Using filesort
1	SIMPLE	pm	ref	post_id,meta_key	meta_key	576	const	37456	Using where
1	SIMPLE	p	eq_ref	PRIMARY,type_status_date	PRIMARY	8	deliciousbrainsdev.pm.post_id	1	Using where
1	SIMPLE	l	ref	PRIMARY,order_id	order_id	8	deliciousbrainsdev.pm.post_id	1	Using index condition; Using where
1	SIMPLE	s	eq_ref	PRIMARY	PRIMARY	8	deliciousbrainsdev.l.key_id	1	NULL

At first glance, this is not easy to explain. Fortunately, SitePoint friends have written a comprehensive guide on understanding the statement.

The most important column is

, which describes how tables are joined. If you see type, it means that MySQL is reading the entire table from disk, increasing the I/O rate and increasing the CPU load. This is called a "full table scan" (more on this later). ALL The

column is also a good indication that MySQL must do because it shows the number of rows it looks at in order to find the result. rows

More information available for optimization is also provided. For example, the EXPLAIN table (pm2), which tells us that we are using wp_postmeta because we require the result to be sorted using the filesort clause in the statement. If we group the queries as well, we will increase the overhead of execution. ORDER BY

Visual Analysis

MySQL Workbench is another convenient and free tool that can be used for such investigations. For databases running on MySQL 5.6 and later, the result of

can be output as JSON, which MySQL Workbench converts to a visual execution plan for the statement: EXPLAIN

It automatically draws your attention by coloring the parts of the query by cost. We can immediately see that there is a serious problem with the joining to the

(alias l) table. wp_woocommerce_software_licences

Solution A full table scan is being performed on a part of the

query, and you should try to avoid this as it uses the non-index column

as a connection between the order_id table and the wp_woocommerce_software_licences table. This is a common problem with slow queries and can be easily solved. wp_posts

Add index

is part of the very important identification data in the table, and if we query like this, we should indeed add an index on that column, otherwise MySQL will scan the table progressively until the desired row is found. Let's add an index and see what it will do: order_id

SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date

Wow, we successfully reduced the query by adding this index for more than 5 seconds, well done!

Learn your query

Check query-connect one by one, sub-query one by one. Did it perform unwanted operations? What optimizations can be made?

In this case, we use order_id to connect the license table to the post table while restricting the statement to the post type of shop_order. This is to force data integrity to ensure we only use the correct order record. However, it is actually the redundant part of the query. We know that having a software license row in the table with order_id related to the WooCommerce order in the post table is a safe bet as this is enforced in the PHP plugin code. Let's delete the connection and see if this will improve the situation:

This is not a big savings, but the query is now less than 3 seconds.

Cache

If your server does not enable MySQL query caching by default, it is worth enabling. This means that MySQL will keep records of all executed statements and their results, and if the same statement is subsequently executed, the cached results will be returned. The cache will not expire because MySQL refreshes the cache when changing the table.

Query Monitor found that our query was run 4 times in a page load, and while enabling MySQL query caching is good, repeated reading of the database in one request should actually be avoided altogether. Static caching in PHP code is a simple and very efficient way to solve this problem. Basically, you get them from the database when the first time you request the results of the database query and store them in the static properties of the class, and subsequent calls will return the result from the static properties:

SELECT
    l.key_id,
    l.order_id,
    l.activation_email,
    l.licence_key,
    l.software_product_id,
    l.software_version,
    l.activations_limit,
    l.created,
    l.renewal_type,
    l.renewal_id,
    l.exempt_domain,
    s.next_payment_date,
    s.status,
    pm2.post_id AS 'product_id',
    pm.meta_value AS 'user_id'
FROM
    oiz6q8a_woocommerce_software_licences l
        INNER JOIN
    oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id
        INNER JOIN
    oiz6q8a_posts p ON p.ID = l.order_id
        INNER JOIN
    oiz6q8a_postmeta pm ON pm.post_id = p.ID
        AND pm.meta_key = '_customer_user'
        INNER JOIN
    oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id'
        AND pm2.meta_value = l.software_product_id
WHERE
    p.post_type = 'shop_order'
        AND pm.meta_value = 279
ORDER BY s.next_payment_date

The lifespan of the cache is the lifespan of the request, and more specifically, the lifespan of the instantiated object. If you want to persist query results between requests, you need to implement persistent object caching. However, your code needs to be responsible for setting up the cache and invalidating the cache entry when the underlying data changes.

Other methods

We can take other ways to try to speed up query execution, which requires more work than just tuning the query or adding indexes. One of the slowest parts of our query is the table connection work from customer ID to product ID, which we have to do for each customer. What if we only perform all connections once, then what should we do if we only need to get customer data when we need it?

You can de-normalize data by creating a table that stores license data as well as user IDs and product IDs for all licenses, just query the table for a specific customer. You need to rebuild the table when using MySQL triggers INSERT/UPDATE/DELETE/

to the license table (or other tables, depending on how the data changes), but this will significantly improve the performance of querying that data .

Similarly, if many connections slow down queries in MySQL, it may be faster to break the query into two or more statements and execute them separately in PHP, and then collect and filter results in your code. Laravel performs similar operations by eagerly loading relationships in Eloquent.

If you have a large amount of data and have many different custom post types, WordPress may be prone to slower queries on the wp_posts table. If you find it slow to query your post type, consider abandoning the custom post type storage model and using a custom table.

Result

With these query optimization methods, we managed to reduce the query time from 8 seconds to just over 2 seconds and reduce the number of calls from 4 to 1. Note that these query times are recorded in our development environment and will be faster in production environments.

I hope this guide will be helpful for you to track and fix slow queries. Query optimization may seem like a terrible task, but once you try and get some quick success, you will start to be fascinated by it and hope to improve further.

The above is the detailed content of How to Optimize SQL Queries for Faster Sites. For more information, please follow other related articles on the PHP Chinese website!