Database performance issues are relatively common on mid-to-large sized WordPress websites. A very slow loading WordPress admin area, or slow page response times for uncached page requests are the most common symptoms.
If your WordPress site is very slow when editing content admin area then you might have slow SQL queries. You may also have other issues, like slow third-party HTTP/API requests.
Causes of a Slow WordPress Website
There are many things that can cause a slow WordPress website, and just about all of them are covered elsewhere in detail. This article doesn’t focus on the standard front-end advice. If you’re optimizing to improve Search Console issues, then this isn’t for you. It’s for WordPress sites with very slow response times, or your admin area takes more than 3 or 4 secs to load each page.
Database Queries
The following scenarios make your WordPress website much more likely to suffer from slow database queries:
- Using WooCommerce and selling a mid-to-high volume of products each day
- B2B sites that receive lots of leads as POST requests
- Community websites that serve uncached pages to logged-in users
- Any WordPress site that serves uncached pages to a subset of users
- Has a large number of plugins, particularly those that do a lot of heavy lifting like WPML, Yoast SEO, Elementor or Fusion Builder.
Third-party HTTP/API Requests
Slow HTTP requests to third-party URL’s is also a common culprit, especially if your site has a large number of premium plugins active. Premium plugins make API requests to external servers that verify the validity of your license keys. When a premium plugin is popular, these license check servers can get overburdened with traffic, I’ve seen it a few times. When this happens, the response times returned slow down, and sometimes take 3 or more seconds each to complete.
Finding Slow Queries
When I work on a WordPress websites with SQL query problems, I always start by narrowing down which SQL queries are taking a long time to complete. To do this, I use the Query Monitor plugin’s Database Queries reports. This plugin is my #1 choice for debugging and diagnosing issues with a WordPress site. It provides everything you’ll need in 95% of situations, with the other 5% sometimes warrant specialized tools.
Using this plugin, you can sort your SQL queries by query time to quickly find any that are slowing down your site. Slow queries are typically split into two categories:
- The query itself isn’t well optimized, or is too complex
- The rows returned by a query are too large
If the query is the problem, then you’ll need to optimize it (if possible) to improve its performance, ultimately making it run faster. The MySQL EXPLAIN statement is very useful for doing this. The EXPLAIN statement in MySQL provides information about how MySQL executes a query to help you understand and optimize it.
To use EXPLAIN with a SQL query, all you need to do is add EXPLAIN to the beginning of the slow query. Once you do this, re-run the query, and you’ll see a table of results with details about the query.
The output returned will probably be confusing at first. Here’s an explanation of what’s going on in the rows returned by EXPLAIN:
Working with MySQL “EXPLAIN”
id– A unique identifier for each select query in the query.select_type– The type of SELECT (simple, primary, derived, etc.).table– The table referred to by the row.partitions– The partitions from which the data is selected, if applicable.type– The join type. Important types include ALL (full table scan), index (index scan), range (indexed range scan), etc.possible_keys– Shows which indices MySQL can choose from to find the rows in this table.key– The index MySQL actually decided to use.key_len– The length of the key used.ref– Shows which columns or constants are compared to the index named in the key column to select rows.rows– The number of rows MySQL believes it must examine to execute the query.filtered– Percentage of rows filtered by the table condition.
Optional extras may be included to provide additional details, such as whether MySQL is using an index, if a temporary table is used, etc. Use the results of the EXPLAIN query to modify the slow WordPress queries. If they’re part of a plugin or theme that’s not custom, you’ll want to use the query filter hook to safely make changes to the SQL query.
Evaluating the Database Contents
Next you’ll want to look at your database tables, and more specifically the rows within them, to find large values that may slow down queries.
Largest Tables in WordPress Database
This query will provide a list of database tables ordered from largest to smallest:
SELECT
table_name as "Table",
table_rows as "Rows",
data_length as "Length",
index_length as "Index",
ROUND(((data_length + index_length) / 1024 / 1024),2) as "Size (mb)"
FROM information_schema.TABLES
WHERE table_schema = database()
ORDER BY `Size (mb)` DESC
The largest table are generally the tables causing issues with query performance (but not always). This query helps you find them.
Largest Custom Field Values in WordPress Database
This query will return the 100 largest rows in the database table used to store custom fields, wp_post_meta. If your installation uses a prefix other than wp_ you’ll need to adjust this.
SELECT
meta_key,
post_id,
(LENGTH(meta_value) / 1024 / 1024) as raw_size,
CONCAT(ROUND((LENGTH(meta_value) / 1024 / 1024),2), 'mb') as size
FROM wp_postmeta
ORDER BY raw_size DESC
LIMIT 100
Largest Post Content Values
Similar to the query that finds large custom fields, this will provide you with the 100 largest posts in your wp_posts table. This is where post, page and custom post type content is stored in the database. The Gutenberg editor’s JSON structure is stored here as well.
SELECT
ID as post_id,
post_title,
(LENGTH(post_content) / 1024) as raw_size,
CONCAT(ROUND((LENGTH(post_content) / 1024),2), 'kb') as size
FROM wp_posts
ORDER BY raw_size DESC
Total Size of Specific Custom Fields
This query finds the total amount of storage used by specific custom fields. You’ll need to modify the LIKE clause by replacing team__% with a rule that matches your custom field key.
SELECT CONCAT(ROUND(SUM(LENGTH(meta_value) / 1024 / 1024), 2), 'mb') as total
FROM wp_postmeta
WHERE meta_key LIKE 'team__%'
Adding Indexes
For advanced situations, it can be beneficial to add INDEXES for situations where you want to find rows with specific column values quickly. This is an advanced method, though, and it can potentially cause issues if not used properly.
Can Decrease Performance
There are situations where adding an INDEX for a database column can decrease performance, so it’s important to fully understand how they work before working with them. Indexes will speed up the querying of rows but use additional writes and more storage space. Generally speaking, indexes can improve the speed of SELECT queries but may slow down INSERT, DELETE, and UPDATE operations.
If you’re unsure of the best ways to add indexes, then I’d recommend starting with the Index WP MySQL For Speed plugin. It allows you to easily add generalized indexes to various tables in your WordPress database and can speed up performance in many cases. If it causes slower queries, then you can revert those changes easily as well.
Advanced Scenarios
Proper use of an INDEX in SQL is highly situational, it depends on factors that vary from one WordPress site to another. If you have business impacting issues with MySQL queries, then I’d suggest working with a professional WP developer with solid database design skills. They’ll be able to analyze your WordPress database and determine the ideal indexes for your site.
Conclusion
The only way to truly improve slow SQL queries in a WordPress database is to analyze the data in the database, optimize the problem areas, and add specific indexes where necessary. Unfortunately, there really isn’t a one-size-fits-all approach that will magically resolve slow query issues for you. Finding the problem and fixing it is and always will be the best approach.
Related Articles
Meet the Author
