Table of Contents
- Introduction to Database Slow Query Logging
- Configuring MySQL Slow Query Logs
- SAVEQUERIES: WordPress Debug Mode
- Query Analysis Tools and Techniques
- Identifying N+1 Queries
- Index Analysis Patterns
- Building an Optimization Workflow
- Automated Monitoring Approaches
- FAQ
Introduction to Database Slow Query Logging
WordPress database slow query logging analysis is essential for understanding performance bottlenecks. Most WordPress sites have database performance issues they don't know about. Queries running 0.5 seconds, 1 second, or longer accumulate quickly across page loads. With 1,000 daily visitors, a 0.5-second query becomes 500 seconds of cumulative database wait time per day.
WordPress slow query logging provides the data necessary to identify and eliminate these bottlenecks. Without proper logging, you're optimizing blindly. With comprehensive logging, you can target the exact queries causing problems.
The WordPress database slow query logging analysis pattern involves three components: MySQL-level logging configuration, WordPress-specific query tracking via SAVEQUERIES, and systematic analysis tools to interpret the data. When combined, these provide complete visibility into database performance.
WP HealthKit includes database analysis in its plugin audits, examining whether plugins execute efficient queries and whether WordPress itself is configured for monitoring. Properly configured slow query logging is a mark of a well-maintained WordPress site.
Configuring MySQL Slow Query Logs
MySQL's native slow query log captures queries exceeding a threshold duration. Configure it in wp-config.php or your hosting control panel:
<?php
// wp-config.php - Enable MySQL slow query logging via WordPress
define('DB_HOST', 'localhost');
define('DB_NAME', 'wordpress_db');
define('DB_USER', 'wp_user');
define('DB_PASSWORD', 'password');
// Force MySQL to slow query log through wp-config
if (!defined('WP_DEBUG_LOG')) {
define('WP_DEBUG', true);
define('WP_DEBUG_LOG', '/wp-content/debug.log');
}
// Configure MySQL parameters (may require server access)
// Or use wp-cli to set them dynamically
For direct MySQL configuration, modify my.cnf or the equivalent on your hosting:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 0.5
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
The slow_query_log_file location depends on your setup. Typical locations include:
- /var/log/mysql/slow-query.log
- /var/log/mysql-slow.log
- C:\Program Files\MySQL\MySQL Server 8.0\data\slow.log (Windows)
Once enabled, MySQL logs all queries exceeding long_query_time (0.5 seconds in the example above). Adjust this threshold based on your performance targets:
# Time = 2026-03-19T10:30:45.123456Z
# User@Host: wordpress[wordpress] @ localhost [127.0.0.1]
# Query_time: 1.234567 Lock_time: 0.000123 Rows_sent: 5 Rows_examined: 45123
SELECT p.ID, p.post_title FROM wp_posts p WHERE p.post_status = 'publish' AND p.post_type = 'post' ORDER BY p.post_date DESC LIMIT 10;
Each slow query entry includes query time, lock time, rows sent, and rows examined. These metrics reveal the nature of the performance problem.
SAVEQUERIES: WordPress Debug Mode
WordPress's SAVEQUERIES constant captures all database queries executed during page load, providing WordPress-level query logging independent of MySQL configuration:
<?php
// wp-config.php
define('WP_DEBUG', true);
define('WP_DEBUG_LOG', '/wp-content/debug.log');
define('SAVEQUERIES', true);
// Later in processing
if (defined('SAVEQUERIES') && SAVEQUERIES) {
// Access all executed queries
global $wpdb;
foreach ($wpdb->queries as $query_data) {
list($query, $time, $call_stack) = $query_data;
// query: the SQL
// time: execution time in milliseconds
// call_stack: where the query was called from
}
}
SAVEQUERIES outputs query data to $wpdb->queries array. Each entry contains the query text, execution time, and call stack. This is invaluable for understanding which WordPress functions trigger which queries:
<?php
// Display query information
add_action('shutdown', function() {
if (!defined('SAVEQUERIES') || !SAVEQUERIES) {
return;
}
global $wpdb;
echo "<!-- Total Queries: " . count($wpdb->queries) . " -->\n";
echo "<!-- Total Time: " . array_sum(array_column($wpdb->queries, 1)) . "ms -->\n";
// Find slowest queries
usort($wpdb->queries, function($a, $b) {
return $b[1] <=> $a[1];
});
echo "<!-- Top 5 Slowest Queries -->\n";
foreach (array_slice($wpdb->queries, 0, 5) as $query_data) {
echo "<!-- Time: " . round($query_data[1], 2) . "ms | Query: " . substr($query_data[0], 0, 50) . "... -->\n";
}
});
SAVEQUERIES is perfect for development and debugging. It doesn't require server configuration changes and works on shared hosting where you can't modify MySQL settings. However, it adds memory overhead, so disable it in production.
Query Analysis Tools and Techniques
Once you have query data, analysis tools help interpret it. The WordPress slow query logging analysis process involves categorizing queries:
<?php
// Analyze query patterns
class QueryAnalyzer {
protected $queries = [];
public function analyze(array $wpdb_queries) {
$stats = [
'total_queries' => 0,
'total_time' => 0,
'by_table' => [],
'by_operation' => [],
'duplicates' => [],
'slow_queries' => [],
];
foreach ($wpdb_queries as list($query, $time, $call_stack)) {
$stats['total_queries']++;
$stats['total_time'] += $time;
// Extract table names
if (preg_match('/FROM\s+`?(\w+)`?/i', $query, $match)) {
$table = $match[1];
if (!isset($stats['by_table'][$table])) {
$stats['by_table'][$table] = ['count' => 0, 'time' => 0];
}
$stats['by_table'][$table]['count']++;
$stats['by_table'][$table]['time'] += $time;
}
// Extract operation type
if (preg_match('/^\s*(SELECT|INSERT|UPDATE|DELETE|JOIN)/i', $query, $match)) {
$op = strtoupper($match[1]);
if (!isset($stats['by_operation'][$op])) {
$stats['by_operation'][$op] = ['count' => 0, 'time' => 0];
}
$stats['by_operation'][$op]['count']++;
$stats['by_operation'][$op]['time'] += $time;
}
// Track slow queries
if ($time > 0.1) {
$stats['slow_queries'][] = [
'query' => $query,
'time' => $time,
'stack' => $call_stack,
];
}
// Detect duplicates
if (!isset($this->queries[$query])) {
$this->queries[$query] = 0;
}
$this->queries[$query]++;
}
// Find duplicate queries
foreach ($this->queries as $query => $count) {
if ($count > 1) {
$stats['duplicates'][] = [
'query' => $query,
'count' => $count,
];
}
}
return $stats;
}
}
// Usage
$analyzer = new QueryAnalyzer();
$report = $analyzer->analyze($wpdb->queries);
echo "Total Queries: " . $report['total_queries'] . "\n";
echo "Total Time: " . round($report['total_time'], 2) . "ms\n";
echo "Duplicate Queries Found: " . count($report['duplicates']) . "\n";
External tools enhance WordPress slow query logging analysis:
pt-query-digest: Analyzes MySQL slow logs and groups similar queries:
# Analyze slow query log
pt-query-digest /var/log/mysql/slow-query.log > slow-query-report.txt
# Example output shows:
# Query 1: SELECT * FROM wp_posts (Appears 245 times, total 12.34s)
# Query 2: SELECT * FROM wp_postmeta (Appears 1050 times, total 5.67s)
MySQL Workbench: Provides visual query analysis and execution plan inspection.
Sequel Pro / TablePlus: Database GUIs for macOS/Windows with query performance tools.
Identifying N+1 Queries
The N+1 query problem is a common WordPress performance issue. Your code makes one query to fetch N items, then N additional queries to fetch related data:
<?php
// N+1 Query Problem
$posts = get_posts(['numberposts' => 10]);
foreach ($posts as $post) {
// This runs 10 additional queries (N+1 pattern)
$author = get_userdata($post->post_author);
echo $author->user_login;
}
// Total: 1 (get_posts) + 10 (get_userdata) = 11 queries
The solution uses WordPress's advanced query capabilities to fetch related data efficiently:
<?php
// Optimized: Fetch all data in 2 queries
$posts = get_posts([
'numberposts' => 10,
'suppress_filters' => false,
]);
// Pre-warm the user cache
$user_ids = wp_list_pluck($posts, 'post_author');
_prime_post_caches([], null, true);
// Now these are cached, no additional queries
foreach ($posts as $post) {
$author = get_userdata($post->post_author);
echo $author->user_login;
}
For custom code, use wpdb directly to fetch in bulk:
<?php
global $wpdb;
// Instead of N queries
$post_ids = [1, 2, 3, 4, 5];
$ids_list = implode(',', array_map('intval', $post_ids));
// Fetch all post_title values at once
$titles = $wpdb->get_results(
"SELECT ID, post_title FROM {$wpdb->posts} WHERE ID IN ($ids_list)"
);
// Results indexed by ID for easy lookup
$titles_by_id = wp_list_pluck($titles, 'post_title', 'ID');
Index Analysis Patterns
Proper database indexing is crucial for query performance. Analyze which queries need indexes:
<?php
// Check for slow queries and suggest indexes
class IndexAnalyzer {
public function getSuggestedIndexes($slow_queries) {
$suggestions = [];
foreach ($slow_queries as $query) {
// Look for WHERE clauses without indexes
if (preg_match('/WHERE\s+(\w+)\s*=/', $query, $match)) {
$column = $match[1];
// Check if column has index
$suggestions[] = "Consider indexing column: $column";
}
// Look for ORDER BY without indexes
if (preg_match('/ORDER BY\s+(\w+)/i', $query, $match)) {
$column = $match[1];
$suggestions[] = "Consider indexing ORDER BY column: $column";
}
}
return array_unique($suggestions);
}
public function checkExistingIndexes($table) {
global $wpdb;
$indexes = $wpdb->get_results(
"SHOW INDEX FROM $table"
);
$index_columns = [];
foreach ($indexes as $index) {
$index_columns[] = $index->Column_name;
}
return $index_columns;
}
}
Common WordPress tables need indexes on frequently queried columns:
-- wp_posts indexes
ALTER TABLE wp_posts ADD INDEX idx_post_type_status (post_type, post_status);
ALTER TABLE wp_posts ADD INDEX idx_post_author_date (post_author, post_date);
ALTER TABLE wp_posts ADD INDEX idx_post_parent (post_parent);
-- wp_postmeta indexes
ALTER TABLE wp_postmeta ADD INDEX idx_post_id_meta_key (post_id, meta_key);
ALTER TABLE wp_postmeta ADD INDEX idx_meta_key (meta_key);
-- wp_usermeta indexes
ALTER TABLE wp_usermeta ADD INDEX idx_user_id_meta_key (user_id, meta_key);
ALTER TABLE wp_usermeta ADD INDEX idx_meta_key (meta_key);
Building an Optimization Workflow
Systematic WordPress slow query logging analysis requires a defined workflow:
Step 1: Enable Logging Configure SAVEQUERIES in wp-config.php for development environments:
<?php
if (getenv('WP_ENV') === 'development') {
define('SAVEQUERIES', true);
define('WP_DEBUG', true);
define('WP_DEBUG_LOG', '/wp-content/debug.log');
}
Step 2: Generate Load Access the site under realistic conditions. Crawl multiple pages, perform typical user actions. Ensure your test data resembles production.
Step 3: Collect and Analyze Export query data from SAVEQUERIES or MySQL logs:
<?php
// Export queries for analysis
if (defined('SAVEQUERIES') && SAVEQUERIES) {
global $wpdb;
$export = json_encode([
'total_queries' => count($wpdb->queries),
'total_time' => array_sum(array_column($wpdb->queries, 1)),
'queries' => $wpdb->queries,
'timestamp' => current_time('mysql'),
]);
file_put_contents('/wp-content/query-analysis.json', $export);
}
Step 4: Identify Priorities Rank slow queries by total impact (count × average_time), not just individual query time:
<?php
$impact = [];
foreach ($slow_queries as $query => $times) {
$count = count($times);
$avg_time = array_sum($times) / $count;
$total_impact = $count * $avg_time;
$impact[$query] = $total_impact;
}
arsort($impact);
foreach (array_slice($impact, 0, 10) as $query => $total_impact) {
echo "Total impact: {$total_impact}ms | Query: " . substr($query, 0, 80) . "\n";
}
Step 5: Optimize and Re-test After optimizations, run the same load test again to measure improvements.
Automated Monitoring Approaches
For ongoing performance monitoring, implement automated systems:
<?php
// WordPress Performance Monitor
class PerformanceMonitor {
public function __construct() {
add_action('shutdown', [$this, 'recordMetrics']);
}
public function recordMetrics() {
if (!defined('SAVEQUERIES') || !SAVEQUERIES) {
return;
}
global $wpdb;
$metrics = [
'timestamp' => time(),
'page_uri' => $_SERVER['REQUEST_URI'],
'query_count' => count($wpdb->queries),
'query_time' => array_sum(array_column($wpdb->queries, 1)),
'memory_used' => memory_get_usage(true) / 1024 / 1024,
];
// Store in custom table or send to monitoring service
update_option('wp_performance_metrics', $metrics);
// Alert if exceeds thresholds
if ($metrics['query_time'] > 1000) { // 1 second
do_action('wp_healthkit_slow_page_detected', $metrics);
}
}
}
new PerformanceMonitor();
Integration with monitoring services provides alerting:
<?php
// Send metrics to external service
add_action('wp_healthkit_slow_page_detected', function($metrics) {
wp_remote_post('https://monitoring-service.com/api/alert', [
'body' => json_encode($metrics),
'headers' => ['Content-Type' => 'application/json'],
]);
});
Advanced Query Analysis and Optimization
After identifying slow queries, the optimization process requires understanding why they're slow. Different types of slow queries have different solutions.
A query might be slow because:
- Missing indexes: The query examines too many rows to find matches
- Inefficient WHERE clauses: The query doesn't properly filter data upfront
- Unnecessary JOINs: The query combines more tables than needed
- Large result sets: The query returns more data than necessary (especially with LIMIT problems)
- Subquery inefficiency: The query executes a subquery multiple times when it could execute once
Each requires a different optimization approach:
<?php
// Analyze slow query to identify optimization opportunity
class SlowQueryOptimizer {
public function analyze_query($query) {
// Check for missing indexes
preg_match('/WHERE\s+(\w+)\s*=/', $query, $matches);
if ($matches) {
// Check if column is indexed
return "Consider indexing: {$matches[1]}";
}
// Check for inefficient ORDER BY
if (preg_match('/ORDER BY.*LIMIT/', $query)) {
return "ORDER BY before LIMIT can be slow";
}
// Check for subquery inefficiency
if (substr_count($query, 'SELECT') > 1) {
return "Multiple subqueries may cause slowness";
}
return "Query structure is reasonable";
}
public function suggest_optimization($query, $execution_time) {
// Heavy queries (> 1 second) might need caching
if ($execution_time > 1) {
return "Consider caching results or using transients";
}
// Medium queries (0.1-1 second) might need indexes
if ($execution_time > 0.1) {
return "Add database indexes or optimize query structure";
}
return "Query is acceptable";
}
}
Real-world optimization often involves incremental improvements. A single optimization might reduce query time from 1 second to 0.8 seconds. Combined with 5 other optimizations, you achieve 80% improvement.
Track optimization results over time. Before optimizing, measure baseline performance. After each optimization, re-measure to quantify improvement. This data helps prioritize future optimizations.
WordPress slow query logging analysis is continuous, not a one-time activity. As your site grows, traffic patterns change, and new plugins install, new performance bottlenecks emerge. Establish ongoing monitoring to catch problems early.
FAQ
What's the difference between SAVEQUERIES and MySQL slow logs?
SAVEQUERIES is WordPress-level query tracking that captures all queries in a PHP array. MySQL slow logs are server-level logs that capture queries exceeding a threshold. Use SAVEQUERIES for development and debugging; use MySQL slow logs for production monitoring.
How do I interpret Rows_examined in MySQL logs?
Rows_examined is the number of rows MySQL examined to produce the result set. A query that examines 100,000 rows but returns 5 rows is inefficient—it probably needs a better index or WHERE clause.
Should I enable SAVEQUERIES in production?
No. SAVEQUERIES stores all query data in a PHP array, adding memory overhead and preventing garbage collection. Use it only for development and debugging. For production, rely on MySQL slow logs instead.
What long_query_time value should I use?
Start with 0.5 seconds to identify obvious problems. As you optimize, lower it to 0.1 seconds. Production sites should target 0.05 seconds or lower. Adjust based on your performance targets and server capabilities.
How do I identify which plugin causes slow queries?
The call_stack field in SAVEQUERIES shows where each query originated. Look for plugin files in the call stack. Slow queries from a plugin are candidates for that plugin's optimization.
Can I analyze historical slow query logs?
Yes. Use pt-query-digest to analyze logs from any time period. Keep archived slow logs for trending analysis to see if performance improves or degrades over time.
Broader Context and Best Practices
Security vulnerabilities in WordPress plugins don't exist in isolation. Each vulnerability represents a potential entry point that attackers chain together to achieve broader compromise. A seemingly minor issue like improper input validation can escalate when combined with a privilege escalation flaw, turning a low-severity finding into a critical breach. This interconnected nature of security weaknesses is why comprehensive auditing matters so much. Rather than checking individual items in isolation, modern security analysis examines how different components interact and where those interactions create unexpected attack surfaces that manual review would miss entirely.
The WordPress plugin ecosystem's open-source nature creates both strengths and challenges for security. Open code allows community review, which catches many issues early. However, it also means attackers can study source code to find exploitable patterns before patches are released. This asymmetry makes proactive security testing essential rather than reactive. Developers who integrate automated security scanning into their development workflow catch vulnerabilities during development, long before code reaches production. The cost of fixing a security issue during development is orders of magnitude lower than addressing it after a public disclosure or active exploitation.
Understanding the attacker's perspective transforms how developers approach security. Attackers don't think in terms of individual functions or classes. They think in terms of data flows, trust boundaries, and privilege transitions. When data crosses from an untrusted context like user input into a trusted context like a database query, that boundary is where vulnerabilities emerge. By mapping these trust boundaries in your plugin architecture, you can systematically identify where validation, sanitization, and authorization checks are needed.
WordPress powers over forty percent of the web, making it the single largest target for automated attacks. Plugin vulnerabilities are the primary vector for these attacks, with Patchstack reporting thousands of new plugin vulnerabilities each year. The scale of the WordPress ecosystem means that even a vulnerability affecting a relatively obscure plugin can impact hundreds of thousands of sites. This reality underscores why every plugin developer has a responsibility to take security seriously.
Broader Industry Context and Best Practices
Security hardening in WordPress extends beyond individual plugin fixes to encompass a holistic defense strategy. Organizations managing multiple WordPress installations benefit from centralized security policies that enforce consistent standards across all sites. This includes automated vulnerability scanning, real-time threat intelligence feeds, and coordinated patch management. WP HealthKit provides the automated scanning infrastructure that makes centralized security monitoring practical, giving teams visibility into vulnerabilities across their entire WordPress portfolio. Regular security assessments should evaluate not just known vulnerabilities but also configuration drift, where settings gradually deviate from security baselines over time, creating subtle but exploitable weaknesses.
Maintaining WordPress security and code quality at scale requires systematic approaches that go beyond individual plugin audits. Organizations managing portfolios of WordPress sites benefit from standardized assessment criteria, automated scanning schedules, and centralized reporting dashboards that aggregate findings across all properties. This systematic approach enables pattern recognition, where recurring issues across multiple sites indicate systemic problems that warrant architectural solutions rather than individual fixes. WP HealthKit provides the foundation for this systematic approach, offering consistent automated assessment that scales from single sites to enterprise portfolios without proportional increases in manual effort or specialized security staffing.
Frequently Asked Questions
How does WP HealthKit detect security vulnerabilities automatically?
WP HealthKit uses 62 verification layers including static analysis, pattern matching, and dependency scanning to identify vulnerabilities in WordPress plugins. The automated scanning catches issues that manual code review would miss, providing comprehensive security coverage across your entire codebase.
What are the most common WordPress plugin security vulnerabilities?
The most frequently discovered vulnerabilities include cross-site scripting through improper output escaping, SQL injection via unparameterized queries, cross-site request forgery from missing nonce verification, and privilege escalation through inadequate capability checks. These four categories account for over seventy percent of all reported plugin vulnerabilities.
How often should I audit my WordPress plugin for security issues?
Security audits should happen at every major release, after significant code changes, and on a regular quarterly schedule. Automated scanning through CI/CD pipelines provides continuous monitoring, while thorough manual reviews should complement automated testing at least twice per year.
Can automated tools replace manual security code review?
Automated tools like WP HealthKit catch the majority of common vulnerability patterns quickly and consistently, but they complement rather than replace manual review. Complex business logic vulnerabilities, architectural issues, and novel attack vectors still benefit from expert human analysis. The ideal approach combines both.
What should I do if a vulnerability is discovered in my plugin?
Follow responsible disclosure practices: verify the vulnerability, develop and test a fix, notify affected users through your update channel, and publish a security advisory. Coordinate with the WordPress security team if the vulnerability is severe. Speed matters — most attackers begin exploitation within days of public disclosure.
Conclusion
WordPress slow query logging analysis is fundamental to optimizing database performance. By configuring MySQL slow logs, enabling SAVEQUERIES for development, and systematically analyzing query patterns, you identify and eliminate the bottlenecks that degrade WordPress performance.
The three-part pattern—MySQL logging configuration, WordPress SAVEQUERIES integration, and systematic analysis—provides complete visibility into database performance. Implement this pattern in your workflow, and performance optimization becomes data-driven rather than guesswork.
WP HealthKit analyzes plugin efficiency by examining database query patterns. When you audit your plugins with WP HealthKit, you get insights into which plugins execute efficient queries and which ones might be causing performance bottlenecks.
Ready to optimize your WordPress database? Upload your plugins to WP HealthKit for detailed analysis of query efficiency and database patterns.
Internal Links
- WordPress Conditional Plugin Loading: Lazy Init Patterns
- WordPress Plugin Admin Pages: Routing and UI Framework