MySQL Slow Query Log

Finding Slow Database Queries

The MySQL/MariaDB slow query log captures queries that exceed a configurable time threshold. This is essential for diagnosing database-related performance issues on your BlitzPress site.

Enable Slow Query Log

Edit the MariaDB configuration:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add or uncomment these lines under [mysqld]:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = 1

Restart MariaDB:

sudo systemctl restart mariadb

Analyzing the Slow Log

Use mysqldumpslow to summarize the log:

# Top 10 slowest queries by time
sudo mysqldumpslow -t 10 -s t /var/log/mysql/slow-query.log

# Top 10 by frequency
sudo mysqldumpslow -t 10 -s c /var/log/mysql/slow-query.log

# Top 10 by rows examined
sudo mysqldumpslow -t 10 -s r /var/log/mysql/slow-query.log

Common Slow Query Patterns in WordPress

Query Pattern	Cause	Fix
SELECT FROM wp_options WHERE autoload	Large autoloaded data	Disable autoload on large options
SELECT FROM wp_postmeta	Missing index on meta queries	Add index on meta_key + meta_value
SELECT FROM wp_posts ORDER BY	Complex WP_Query without proper index	Optimize the query or add custom index
DELETE FROM wp_actionscheduler_*	WooCommerce action scheduler cleanup	Run cleanup: wp action-scheduler clean

Using EXPLAIN

For specific slow queries, use EXPLAIN to see the execution plan:

sudo mysql YOUR_DB -e "EXPLAIN SELECT * FROM wp_postmeta WHERE meta_key='_thumbnail_id';"

Look for type: ALL (full table scan) — these need an index.

Adding Custom Indexes

sudo mysql YOUR_DB -e "CREATE INDEX idx_meta_key ON wp_postmeta(meta_key(191));"

Only add indexes after confirming the query pattern is common and slow. Unnecessary indexes slow down writes.

Disable When Done

The slow query log can grow large on busy sites. Disable it after your investigation:

sudo mysql -e "SET GLOBAL slow_query_log = 0;"