MySQL 8 Features for WordPress: EXPLAIN ANALYZE and Window Functions

This article is a localized edition of the Japanese original. Read the Japanese original

MySQL 8 added a number of features that are useful when you run a WordPress site. WordPress itself does not use them automatically, though. WordPress core is written to run on older MySQL versions too, so the benefits show up mainly when "a site administrator inspects or tunes the database" and when "you write code just for your own site."

This article goes through the features you can actually use against a WordPress database, grouped by purpose, with SQL examples.

Background: where the versions stand

• MySQL 8.0 reached end of support in April 2026. If you are setting something up now, the long-term support release MySQL 8.4 LTS is the practical choice (as of November 2026). Every feature in this article works on both 8.0 and 8.4.
• WordPress.org recommends "MySQL 8.0 or greater, or MariaDB 10.11 or greater." At the same time, MySQL 5.5.5 is still listed as the legacy minimum for older environments (as of November 2026).
• Do not write queries that depend on the features below in plugins or themes you distribute. Your users' databases may be older MySQL or MariaDB. MariaDB has window functions and CTEs, but EXPLAIN ANALYZE and invisible indexes use different syntax there.

You can check the current version with the query below. You can also see it under "Tools" → "Site Health" → "Info", in the "Database" section.

SELECT VERSION();

Points to watch when migrating from 5.7 or earlier (authentication method, collation, sql_mode) are covered in Moving WordPress to MySQL 8: what changes and how to handle it.

Find slow queries: Performance Schema and the sys schema

You can see which queries take up database time from the Performance Schema statistics. Performance Schema is enabled by default in MySQL 8. Note that performance_schema is a variable that can only be set at server startup; you cannot switch it with SET GLOBAL.

For a readable view, use the sys schema views. sys.statement_analysis is sorted by total execution time, longest first.

-- Top 10 queries by total execution time (per normalized query)
SELECT query, db, exec_count, total_latency, avg_latency, rows_examined_avg
FROM sys.statement_analysis
WHERE db = 'wordpress'
LIMIT 10;

If you query the underlying summary table directly, note that the time column (SUM_TIMER_WAIT) is in picoseconds.

SELECT DIGEST_TEXT,
       COUNT_STAR AS exec_count,
       ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'wordpress'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

On WordPress sites, the autoloaded wp_options query, filters that use wp_postmeta (meta_query), and search queries often end up near the top.

Measure the execution plan: EXPLAIN ANALYZE

EXPLAIN ANALYZE, available from MySQL 8.0.18, actually runs the query and shows the real row counts and timings for each step. Plain EXPLAIN only shows estimates, so this helps you find the places where the estimate and reality diverge.

Here is an example using the same shape of query that a WP_Query filtering on a custom field generates.

EXPLAIN ANALYZE
SELECT p.ID
FROM wp_posts AS p
INNER JOIN wp_postmeta AS m ON p.ID = m.post_id
WHERE p.post_type = 'post'
  AND p.post_status = 'publish'
  AND m.meta_key = 'price'
  AND m.meta_value = '1000'
ORDER BY p.post_date DESC
LIMIT 10;

Look at actual time and rows in the output and find the steps that read many rows (a Table scan, or an Index lookup with a large rows value). Because EXPLAIN ANALYZE really executes the query, try heavy queries outside production peak hours.

If your site uses a table prefix other than wp_, adjust the table names.

Test indexes safely: invisible indexes

MySQL 8 lets you make an index "invisible to the optimizer" without dropping it. Before you delete an index you think is unused, you can make it invisible and watch what happens, so a performance regression is easy to undo.

First, use the sys schema to find indexes that have not been used once since the server started. Right after a restart there is little data, so run the site normally for a few days before checking.

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema = 'wordpress';

When you find a candidate, make it invisible and check behavior and speed. If something goes wrong, just make it visible again.

-- Example: make an index added by a plugin invisible (replace with the real name)
ALTER TABLE wp_example_log ALTER INDEX idx_created INVISIBLE;

-- Check the state
SELECT TABLE_NAME, INDEX_NAME, IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'wordpress' AND TABLE_NAME = 'wp_example_log';

-- Revert
ALTER TABLE wp_example_log ALTER INDEX idx_created VISIBLE;

Do not make invisible or drop the standard indexes on WordPress core tables (wp_posts, wp_postmeta and so on); core queries rely on them. When you add an index of your own, the safe order is: confirm the effect with EXPLAIN ANALYZE before putting it into production, and if it turns out unnecessary, make it invisible and watch the impact.

Write reports in a single query: window functions and CTEs

Since MySQL 8.0 you can use window functions (ROW_NUMBER(), SUM() OVER and so on) and common table expressions (the WITH clause). You can write admin reports and aggregations without looping in PHP.

This example counts published posts per month and adds a running total.

WITH monthly AS (
  SELECT DATE_FORMAT(post_date, '%Y-%m') AS ym, COUNT(*) AS cnt
  FROM wp_posts
  WHERE post_type = 'post' AND post_status = 'publish'
  GROUP BY ym
)
SELECT ym, cnt, SUM(cnt) OVER (ORDER BY ym) AS running_total
FROM monthly
ORDER BY ym;

This example picks the three latest posts for each author.

SELECT post_author, ID, post_title, post_date
FROM (
  SELECT post_author, ID, post_title, post_date,
         ROW_NUMBER() OVER (PARTITION BY post_author ORDER BY post_date DESC) AS rn
  FROM wp_posts
  WHERE post_type = 'post' AND post_status = 'publish'
) AS t
WHERE rn <= 3
ORDER BY post_author, post_date DESC;

To use this in code just for your own site (your theme's functions.php or your own plugin), build the table name with $wpdb->posts and run it with $wpdb->get_results().

<?php
global $wpdb;

$sql = "WITH monthly AS (
    SELECT DATE_FORMAT(post_date, '%Y-%m') AS ym, COUNT(*) AS cnt
    FROM {$wpdb->posts}
    WHERE post_type = 'post' AND post_status = 'publish'
    GROUP BY ym
)
SELECT ym, cnt, SUM(cnt) OVER (ORDER BY ym) AS running_total
FROM monthly
ORDER BY ym";

// Fixed SQL with no user input, so prepare is not needed.
// If you include input values, use $wpdb->prepare().
$rows = $wpdb->get_results( $sql );

In SQL passed through $wpdb->prepare(), % is treated as a placeholder, so %Y and the like in DATE_FORMAT must be written as %%Y. The example above does not use prepare(), so it works as is.

Keep settings across restarts: SET PERSIST

MySQL 8's SET PERSIST changes a setting on the running server and also writes it to mysqld-auto.cnf in the data directory, so the setting survives a restart. SET GLOBAL is lost on restart.

This example turns on the slow query log to investigate slow WordPress queries.

SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 1;

-- Check where the log is written
SHOW VARIABLES LIKE 'slow_query_log_file';

-- List persisted settings
SELECT * FROM performance_schema.persisted_variables;

-- Stop persisting (the running value stays as is)
RESET PERSIST long_query_time;

SET PERSIST needs privileges such as SYSTEM_VARIABLES_ADMIN. It is usually not available on shared hosting or managed databases; in that case, change the setting from the service's control panel.

Manage privileges as a group: roles

MySQL 8 lets you bundle privileges into a "role" and assign it to users. A typical case is a read-only user for an analytics tool or backups that only reads the WordPress database.

CREATE ROLE 'wp_readonly';
GRANT SELECT ON wordpress.* TO 'wp_readonly';

CREATE USER 'report'@'localhost' IDENTIFIED BY 'your-strong-password';
GRANT 'wp_readonly' TO 'report'@'localhost';
SET DEFAULT ROLE 'wp_readonly' TO 'report'@'localhost';

The user WordPress itself connects with (DB_USER in wp-config.php) needs privileges that include creating and altering tables. Use the read-only user only for things other than WordPress itself.

Adding columns to large tables: ALGORITHM=INSTANT

From MySQL 8.0.12, adding a column that meets the conditions can be done without rebuilding the table (from 8.0.29 you can also add it at positions other than the end). This shortens lock time when you add a column to a large plugin table, such as one that stores access logs.

-- If the change cannot run as INSTANT, it errors out and nothing is changed
ALTER TABLE wp_example_log ADD COLUMN source VARCHAR(20) NULL, ALGORITHM=INSTANT;

If you state ALGORITHM=INSTANT explicitly, a change that needs a rebuild stops with an error, which prevents an unintended long lock. The structure of core tables is managed by WordPress updates, so add your own columns only to tables you created yourself.

Before you start

• Back up the database before trying anything in production. With WP-CLI you can export it with wp db export.
• general_log (which records every query) adds load and grows the log quickly, so do not leave it on. Use it only for short investigations and turn it off when you are done.
• On InnoDB, OPTIMIZE TABLE rebuilds the table and takes a long time on large tables. It is safer to run it when needed, for example after a large deletion, rather than on a schedule.
• Older material still shows NO_AUTO_CREATE_USER in sql_mode, but it was removed in MySQL 8.0 and setting it causes an error.

Summary

• WordPress core does not depend on MySQL 8 features, so the benefits are in administrator investigation and tuning, and in code written just for your own site
• Find slow queries with sys.statement_analysis and measure them with EXPLAIN ANALYZE
• Before dropping an index, check the impact by making it invisible
• Reports and aggregations can be written as a single query with window functions and CTEs
• SET PERSIST, roles and ALGORITHM=INSTANT make managing settings, privileges and column additions easier
• MySQL 8.4 LTS is the practical migration target (as of November 2026)

• A practical guide to reading WordPress queries with MySQL EXPLAIN and optimizing them

Questions about WordPress? The publisher of this site is based in Japan and replies in Japanese. Contact (Japanese)