WordPress Database Optimization: From Queries to Indexes
The database is the heart of every WordPress site, and its performance directly impacts your site's speed and scalability. A poorly optimized database can turn even the most powerful server into a sluggish system. This comprehensive guide covers everything from basic maintenance to advanced optimization techniques that can improve query performance by 10x or more.
Whether you're dealing with a growing blog or a high-traffic WooCommerce store, understanding database optimization is crucial for maintaining fast page loads and efficient resource usage. We'll explore query optimization, indexing strategies, maintenance routines, and scaling solutions that keep your WordPress database running at peak performance.
Table of Contents
- Understanding WordPress Database Structure
- Query Optimization Techniques
- Indexing Strategies
- Database Maintenance
- Scaling Database Performance
- Monitoring and Troubleshooting
- Advanced Optimization Techniques
Understanding WordPress Database Structure
Core WordPress Tables
Understanding the database structure is essential for optimization:
-- Core WordPress tables and their purposes
wp_posts -- Posts, pages, attachments, revisions, menu items
wp_postmeta -- Custom fields and post metadata
wp_users -- User accounts
wp_usermeta -- User metadata and preferences
wp_terms -- Categories, tags, and custom taxonomy terms
wp_term_taxonomy -- Taxonomy relationships
wp_term_relationships -- Post-to-term relationships
wp_options -- Site settings and transients
wp_comments -- Comments and pingbacks
wp_commentmeta -- Comment metadata
wp_links -- Legacy blogroll feature (rarely used)
Table Relationships
-- Key relationships in WordPress database
Posts ←→ Postmeta (1:N)
Posts ←→ Terms (N:N via term_relationships)
Posts ←→ Users (N:1)
Posts ←→ Comments (1:N)
Users ←→ Usermeta (1:N)
Terms ←→ Term_taxonomy (1:1)
Comments ←→ Commentmeta (1:N)
Common Performance Bottlenecks
// Identify slow queries
add_action('shutdown', function() {
if (!defined('SAVEQUERIES') || !SAVEQUERIES) {
return;
}
global $wpdb;
$slow_queries = [];
foreach ($wpdb->queries as $query) {
if ($query[1] > 0.05) { // Queries over 50ms
$slow_queries[] = [
'sql' => $query[0],
'time' => $query[1],
'trace' => $query[2]
];
}
}
if (!empty($slow_queries)) {
error_log('Slow queries detected: ' . print_r($slow_queries, true));
}
});
Query Optimization Techniques
Optimizing WP_Query
// BAD: Inefficient query
$posts = new WP_Query([
'posts_per_page' => -1, // Getting all posts
'meta_key' => 'views',
'orderby' => 'meta_value_num',
'order' => 'DESC'
]);
// GOOD: Optimized query
$posts = new WP_Query([
'posts_per_page' => 10,
'meta_key' => 'views',
'orderby' => 'meta_value_num',
'order' => 'DESC',
'no_found_rows' => true, // Skip counting total rows
'update_post_meta_cache' => false, // Skip meta cache if not needed
'update_post_term_cache' => false, // Skip term cache if not needed
'fields' => 'ids' // Get only IDs if that's all you need
]);
Custom Query Optimization
// Query optimization class
class DB_Query_Optimizer {
/**
* Get posts with specific meta efficiently
*/
public static function get_posts_with_meta($meta_key, $meta_value, $limit = 10) {
global $wpdb;
// Use direct query with proper indexes
$query = $wpdb->prepare("
SELECT p.*
FROM {$wpdb->posts} p
INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
WHERE p.post_status = 'publish'
AND p.post_type = 'post'
AND pm.meta_key = %s
AND pm.meta_value = %s
ORDER BY p.post_date DESC
LIMIT %d
", $meta_key, $meta_value, $limit);
$results = $wpdb->get_results($query);
// Prime caches efficiently
if ($results) {
$post_ids = wp_list_pluck($results, 'ID');
_prime_post_caches($post_ids, true, true);
}
return $results;
}
/**
* Batch update post meta
*/
public static function batch_update_meta($post_ids, $meta_key, $meta_value) {
global $wpdb;
if (empty($post_ids)) {
return false;
}
$post_ids = array_map('intval', $post_ids);
$ids_string = implode(',', $post_ids);
// Delete existing meta
$wpdb->query($wpdb->prepare("
DELETE FROM {$wpdb->postmeta}
WHERE post_id IN ($ids_string)
AND meta_key = %s
", $meta_key));
// Insert new meta values in batch
$values = [];
foreach ($post_ids as $id) {
$values[] = $wpdb->prepare("(%d, %s, %s)", $id, $meta_key, $meta_value);
}
if (!empty($values)) {
$wpdb->query("
INSERT INTO {$wpdb->postmeta} (post_id, meta_key, meta_value)
VALUES " . implode(',', $values)
);
}
// Clear cache
foreach ($post_ids as $id) {
clean_post_cache($id);
}
return true;
}
/**
* Efficient taxonomy queries
*/
public static function get_posts_in_terms($term_ids, $taxonomy = 'category', $limit = 10) {
global $wpdb;
if (!is_array($term_ids)) {
$term_ids = [$term_ids];
}
$term_ids = array_map('intval', $term_ids);
$placeholders = implode(',', array_fill(0, count($term_ids), '%d'));
$query = $wpdb->prepare("
SELECT DISTINCT p.*
FROM {$wpdb->posts} p
INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE p.post_status = 'publish'
AND p.post_type = 'post'
AND tt.taxonomy = %s
AND tt.term_id IN ($placeholders)
ORDER BY p.post_date DESC
LIMIT %d
", $taxonomy, ...$term_ids, $limit);
return $wpdb->get_results($query);
}
}
Query Caching Strategies
// Advanced query caching
class Query_Cache_Manager {
private static $cache_group = 'db_queries';
private static $cache_time = 3600; // 1 hour
/**
* Cache complex query results
*/
public static function cached_query($cache_key, $callback, $expire = null) {
$expire = $expire ?: self::$cache_time;
// Try to get from cache
$cached = wp_cache_get($cache_key, self::$cache_group);
if (false !== $cached) {
return $cached;
}
// Execute query
$result = call_user_func($callback);
// Cache result
wp_cache_set($cache_key, $result, self::$cache_group, $expire);
return $result;
}
/**
* Get popular posts with caching
*/
public static function get_popular_posts($days = 7, $limit = 10) {
$cache_key = sprintf('popular_posts_%d_%d', $days, $limit);
return self::cached_query($cache_key, function() use ($days, $limit) {
global $wpdb;
return $wpdb->get_results($wpdb->prepare("
SELECT p.*, COUNT(pm.meta_id) as view_count
FROM {$wpdb->posts} p
INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
WHERE p.post_status = 'publish'
AND p.post_type = 'post'
AND pm.meta_key = 'post_views'
AND p.post_date > DATE_SUB(NOW(), INTERVAL %d DAY)
GROUP BY p.ID
ORDER BY view_count DESC
LIMIT %d
", $days, $limit));
});
}
/**
* Clear query cache
*/
public static function clear_cache($specific_key = null) {
if ($specific_key) {
return wp_cache_delete($specific_key, self::$cache_group);
}
// Clear all query cache
return wp_cache_flush_group(self::$cache_group);
}
}
Indexing Strategies
Custom Index Implementation
// Database index manager
class DB_Index_Manager {
/**
* Add custom indexes for better performance
*/
public static function add_custom_indexes() {
global $wpdb;
$indexes = [
// Postmeta indexes
[
'table' => $wpdb->postmeta,
'name' => 'meta_key_value',
'columns' => 'meta_key(191), meta_value(100)',
'unique' => false
],
// Posts indexes
[
'table' => $wpdb->posts,
'name' => 'type_status_date',
'columns' => 'post_type, post_status, post_date',
'unique' => false
],
[
'table' => $wpdb->posts,
'name' => 'post_parent_type',
'columns' => 'post_parent, post_type',
'unique' => false
],
// Comments indexes
[
'table' => $wpdb->comments,
'name' => 'comment_approved_date',
'columns' => 'comment_approved, comment_date_gmt',
'unique' => false
],
// Options indexes
[
'table' => $wpdb->options,
'name' => 'autoload',
'columns' => 'autoload',
'unique' => false
]
];
foreach ($indexes as $index) {
self::create_index($index);
}
}
/**
* Create index if it doesn't exist
*/
private static function create_index($index_data) {
global $wpdb;
$table = $index_data['table'];
$index_name = $index_data['name'];
// Check if index exists
$existing = $wpdb->get_results("
SHOW INDEX FROM {$table}
WHERE Key_name = '{$index_name}'
");
if (empty($existing)) {
$unique = $index_data['unique'] ? 'UNIQUE' : '';
$sql = "ALTER TABLE {$table} ADD {$unique} INDEX {$index_name} ({$index_data['columns']})";
$wpdb->query($sql);
error_log("Created index: {$index_name} on table {$table}");
}
}
/**
* Analyze index usage
*/
public static function analyze_index_usage() {
global $wpdb;
$tables = [
$wpdb->posts,
$wpdb->postmeta,
$wpdb->terms,
$wpdb->term_relationships,
$wpdb->options
];
$report = [];
foreach ($tables as $table) {
// Get table size
$size = $wpdb->get_row("
SELECT
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
ROUND((data_length / 1024 / 1024), 2) AS data_mb,
ROUND((index_length / 1024 / 1024), 2) AS index_mb
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
AND table_name = '{$table}'
");
// Get indexes
$indexes = $wpdb->get_results("
SHOW INDEX FROM {$table}
");
$report[$table] = [
'size' => $size,
'indexes' => $indexes,
'index_count' => count(array_unique(wp_list_pluck($indexes, 'Key_name')))
];
}
return $report;
}
/**
* Remove redundant indexes
*/
public static function optimize_indexes() {
global $wpdb;
// Find duplicate indexes
$duplicates = $wpdb->get_results("
SELECT
table_name,
index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns
FROM information_schema.statistics
WHERE table_schema = DATABASE()
GROUP BY table_name, index_name
HAVING COUNT(*) > 1
");
foreach ($duplicates as $duplicate) {
error_log("Potential duplicate index: {$duplicate->index_name} on {$duplicate->table_name}");
}
}
}
Query Performance Analysis
// Query performance analyzer
class Query_Performance_Analyzer {
/**
* Explain query execution plan
*/
public static function explain_query($sql) {
global $wpdb;
$explanation = $wpdb->get_results("EXPLAIN " . $sql);
$analysis = [
'query' => $sql,
'plan' => $explanation,
'warnings' => []
];
foreach ($explanation as $row) {
// Check for performance issues
if ($row->type === 'ALL') {
$analysis['warnings'][] = "Full table scan on {$row->table}";
}
if ($row->Extra && strpos($row->Extra, 'Using filesort') !== false) {
$analysis['warnings'][] = "Filesort required for {$row->table}";
}
if ($row->Extra && strpos($row->Extra, 'Using temporary') !== false) {
$analysis['warnings'][] = "Temporary table required for {$row->table}";
}
if (!$row->key) {
$analysis['warnings'][] = "No index used for {$row->table}";
}
}
return $analysis;
}
/**
* Profile query execution
*/
public static function profile_query($sql) {
global $wpdb;
// Enable profiling
$wpdb->query("SET profiling = 1");
// Execute query
$start = microtime(true);
$wpdb->query($sql);
$duration = microtime(true) - $start;
// Get profile data
$profile = $wpdb->get_results("SHOW PROFILE");
// Disable profiling
$wpdb->query("SET profiling = 0");
return [
'duration' => $duration,
'profile' => $profile,
'stages' => self::analyze_profile_stages($profile)
];
}
/**
* Analyze profile stages
*/
private static function analyze_profile_stages($profile) {
$stages = [];
$total_time = 0;
foreach ($profile as $stage) {
$total_time += $stage->Duration;
if (!isset($stages[$stage->Status])) {
$stages[$stage->Status] = 0;
}
$stages[$stage->Status] += $stage->Duration;
}
// Calculate percentages
foreach ($stages as $stage => &$time) {
$time = [
'duration' => $time,
'percentage' => ($time / $total_time) * 100
];
}
return $stages;
}
}
Database Maintenance
Automated Maintenance System
// Comprehensive database maintenance
class DB_Maintenance_Manager {
public function __construct() {
// Schedule maintenance tasks
if (!wp_next_scheduled('db_daily_maintenance')) {
wp_schedule_event(time(), 'daily', 'db_daily_maintenance');
}
if (!wp_next_scheduled('db_weekly_maintenance')) {
wp_schedule_event(time(), 'weekly', 'db_weekly_maintenance');
}
add_action('db_daily_maintenance', [$this, 'daily_maintenance']);
add_action('db_weekly_maintenance', [$this, 'weekly_maintenance']);
}
/**
* Daily maintenance tasks
*/
public function daily_maintenance() {
$this->clean_transients();
$this->clean_expired_sessions();
$this->optimize_autoloaded_options();
$this->clean_post_meta_duplicates();
}
/**
* Weekly maintenance tasks
*/
public function weekly_maintenance() {
$this->clean_revisions();
$this->clean_orphaned_data();
$this->optimize_tables();
$this->analyze_table_health();
}
/**
* Clean expired transients
*/
private function clean_transients() {
global $wpdb;
// Delete expired transients
$wpdb->query("
DELETE FROM {$wpdb->options}
WHERE option_name LIKE '_transient_timeout_%'
AND option_value < UNIX_TIMESTAMP()
");
// Delete orphaned transient data
$wpdb->query("
DELETE FROM {$wpdb->options}
WHERE option_name LIKE '_transient_%'
AND option_name NOT LIKE '_transient_timeout_%'
AND option_name NOT IN (
SELECT CONCAT('_transient_', SUBSTRING(option_name, 19))
FROM (SELECT option_name FROM {$wpdb->options}) AS temp
WHERE option_name LIKE '_transient_timeout_%'
)
");
$cleaned = $wpdb->rows_affected;
if ($cleaned > 0) {
error_log("Cleaned {$cleaned} expired transients");
}
}
/**
* Clean post revisions
*/
private function clean_revisions($keep_revisions = 5) {
global $wpdb;
// Get posts with excessive revisions
$posts_with_revisions = $wpdb->get_results("
SELECT post_parent, COUNT(*) as revision_count
FROM {$wpdb->posts}
WHERE post_type = 'revision'
GROUP BY post_parent
HAVING revision_count > {$keep_revisions}
");
foreach ($posts_with_revisions as $post) {
// Keep only the latest X revisions
$revisions_to_keep = $wpdb->get_col($wpdb->prepare("
SELECT ID
FROM {$wpdb->posts}
WHERE post_parent = %d
AND post_type = 'revision'
ORDER BY post_modified DESC
LIMIT %d
", $post->post_parent, $keep_revisions));
if (!empty($revisions_to_keep)) {
$ids_to_keep = implode(',', array_map('intval', $revisions_to_keep));
// Delete old revisions
$wpdb->query($wpdb->prepare("
DELETE FROM {$wpdb->posts}
WHERE post_parent = %d
AND post_type = 'revision'
AND ID NOT IN ({$ids_to_keep})
", $post->post_parent));
}
}
}
/**
* Clean orphaned metadata
*/
private function clean_orphaned_data() {
global $wpdb;
// Clean orphaned postmeta
$wpdb->query("
DELETE pm FROM {$wpdb->postmeta} pm
LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
WHERE p.ID IS NULL
");
$postmeta_cleaned = $wpdb->rows_affected;
// Clean orphaned commentmeta
$wpdb->query("
DELETE cm FROM {$wpdb->commentmeta} cm
LEFT JOIN {$wpdb->comments} c ON c.comment_ID = cm.comment_id
WHERE c.comment_ID IS NULL
");
$commentmeta_cleaned = $wpdb->rows_affected;
// Clean orphaned term relationships
$wpdb->query("
DELETE tr FROM {$wpdb->term_relationships} tr
LEFT JOIN {$wpdb->posts} p ON p.ID = tr.object_id
WHERE p.ID IS NULL
");
$term_rel_cleaned = $wpdb->rows_affected;
error_log("Cleaned orphaned data: {$postmeta_cleaned} postmeta, {$commentmeta_cleaned} commentmeta, {$term_rel_cleaned} term relationships");
}
/**
* Optimize autoloaded options
*/
private function optimize_autoloaded_options() {
global $wpdb;
// Get size of autoloaded options
$autoload_size = $wpdb->get_var("
SELECT SUM(LENGTH(option_value))
FROM {$wpdb->options}
WHERE autoload = 'yes'
");
$autoload_size_mb = round($autoload_size / 1024 / 1024, 2);
if ($autoload_size_mb > 1) {
// Find large autoloaded options
$large_options = $wpdb->get_results("
SELECT option_name, LENGTH(option_value) as size
FROM {$wpdb->options}
WHERE autoload = 'yes'
AND LENGTH(option_value) > 10000
ORDER BY size DESC
LIMIT 20
");
foreach ($large_options as $option) {
// Check if option is actually needed on every page load
if ($this->can_disable_autoload($option->option_name)) {
$wpdb->update(
$wpdb->options,
['autoload' => 'no'],
['option_name' => $option->option_name]
);
error_log("Disabled autoload for option: {$option->option_name} (size: {$option->size} bytes)");
}
}
}
}
/**
* Check if option can have autoload disabled
*/
private function can_disable_autoload($option_name) {
// Options that should always autoload
$always_autoload = [
'siteurl',
'home',
'blogname',
'blogdescription',
'users_can_register',
'default_role',
'timezone_string',
'WPLANG',
'stylesheet',
'template',
'active_plugins'
];
if (in_array($option_name, $always_autoload)) {
return false;
}
// Theme mods should usually autoload
if (strpos($option_name, 'theme_mods_') === 0) {
return false;
}
// Large arrays and objects can often have autoload disabled
if (strpos($option_name, '_transient_') === 0 ||
strpos($option_name, '_site_transient_') === 0) {
return true;
}
return true;
}
/**
* Optimize database tables
*/
private function optimize_tables() {
global $wpdb;
$tables = $wpdb->get_col("
SELECT table_name
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND table_name LIKE '{$wpdb->prefix}%'
");
foreach ($tables as $table) {
$wpdb->query("OPTIMIZE TABLE {$table}");
}
error_log("Optimized " . count($tables) . " database tables");
}
/**
* Analyze table health
*/
private function analyze_table_health() {
global $wpdb;
$health_report = [];
$tables_info = $wpdb->get_results("
SELECT
table_name,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
ROUND((data_free / 1024 / 1024), 2) AS free_mb,
table_rows,
avg_row_length,
ROUND(((data_free / (data_length + index_length)) * 100), 2) AS fragmentation
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND table_name LIKE '{$wpdb->prefix}%'
ORDER BY size_mb DESC
");
foreach ($tables_info as $table) {
if ($table->fragmentation > 10) {
$health_report[] = "Table {$table->table_name} is {$table->fragmentation}% fragmented";
}
if ($table->size_mb > 100) {
$health_report[] = "Table {$table->table_name} is large ({$table->size_mb} MB)";
}
}
if (!empty($health_report)) {
error_log("Database health issues: " . implode(', ', $health_report));
}
return $tables_info;
}
/**
* Clean duplicate post meta
*/
private function clean_post_meta_duplicates() {
global $wpdb;
// Find and remove duplicate post meta entries
$duplicates = $wpdb->get_results("
SELECT post_id, meta_key, COUNT(*) as count
FROM {$wpdb->postmeta}
GROUP BY post_id, meta_key
HAVING count > 1
");
foreach ($duplicates as $duplicate) {
// Keep the latest meta entry
$meta_ids = $wpdb->get_col($wpdb->prepare("
SELECT meta_id
FROM {$wpdb->postmeta}
WHERE post_id = %d
AND meta_key = %s
ORDER BY meta_id DESC
", $duplicate->post_id, $duplicate->meta_key));
if (count($meta_ids) > 1) {
// Keep the first (latest) and delete the rest
array_shift($meta_ids);
if (!empty($meta_ids)) {
$ids_to_delete = implode(',', array_map('intval', $meta_ids));
$wpdb->query("DELETE FROM {$wpdb->postmeta} WHERE meta_id IN ({$ids_to_delete})");
}
}
}
}
}
// Initialize maintenance
new DB_Maintenance_Manager();
Scaling Database Performance
Database Replication Setup
// MySQL replication manager
class DB_Replication_Manager {
private $write_db;
private $read_dbs = [];
private $current_read_db = 0;
public function __construct() {
if (defined('DB_WRITE_HOST') && defined('DB_READ_HOSTS')) {
$this->setup_connections();
add_filter('query', [$this, 'route_query'], 1);
}
}
/**
* Setup database connections
*/
private function setup_connections() {
// Write database
$this->write_db = new wpdb(
DB_USER,
DB_PASSWORD,
DB_NAME,
DB_WRITE_HOST
);
// Read databases
$read_hosts = explode(',', DB_READ_HOSTS);
foreach ($read_hosts as $host) {
$this->read_dbs[] = new wpdb(
DB_USER,
DB_PASSWORD,
DB_NAME,
trim($host)
);
}
}
/**
* Route queries to appropriate database
*/
public function route_query($query) {
global $wpdb;
if ($this->is_write_query($query)) {
$wpdb = $this->write_db;
} else {
$wpdb = $this->get_read_db();
}
return $query;
}
/**
* Determine if query is a write operation
*/
private function is_write_query($query) {
$query = trim($query);
$write_operations = ['INSERT', 'UPDATE', 'DELETE', 'REPLACE', 'CREATE', 'ALTER', 'DROP', 'TRUNCATE'];
foreach ($write_operations as $op) {
if (stripos($query, $op) === 0) {
return true;
}
}
return false;
}
/**
* Get read database using round-robin
*/
private function get_read_db() {
$db = $this->read_dbs[$this->current_read_db];
$this->current_read_db++;
if ($this->current_read_db >= count($this->read_dbs)) {
$this->current_read_db = 0;
}
return $db;
}
}
Query Result Caching Layer
// Advanced caching layer
class DB_Cache_Layer {
private $cache_enabled = true;
private $cache_stats = [
'hits' => 0,
'misses' => 0,
'writes' => 0
];
public function __construct() {
add_filter('query', [$this, 'maybe_cache_query'], 10);
add_action('shutdown', [$this, 'log_cache_stats']);
}
/**
* Cache SELECT queries
*/
public function maybe_cache_query($query) {
if (!$this->should_cache_query($query)) {
return $query;
}
$cache_key = $this->generate_cache_key($query);
$cached = wp_cache_get($cache_key, 'db_cache');
if (false !== $cached) {
$this->cache_stats['hits']++;
// Return cached result
add_filter('query_results', function($results) use ($cached) {
return $cached;
}, 10, 1);
// Skip actual query execution
return "SELECT 'cached' as cached";
}
$this->cache_stats['misses']++;
// Cache the results after query execution
add_filter('query_results', function($results) use ($cache_key, $query) {
if ($results && $results !== ['cached']) {
$ttl = $this->get_cache_ttl($query);
wp_cache_set($cache_key, $results, 'db_cache', $ttl);
$this->cache_stats['writes']++;
}
return $results;
}, 10, 1);
return $query;
}
/**
* Determine if query should be cached
*/
private function should_cache_query($query) {
$query = trim($query);
// Only cache SELECT queries
if (stripos($query, 'SELECT') !== 0) {
return false;
}
// Don't cache queries with SQL_CALC_FOUND_ROWS
if (stripos($query, 'SQL_CALC_FOUND_ROWS') !== false) {
return false;
}
// Don't cache user-specific queries
if (stripos($query, 'user') !== false) {
return false;
}
return true;
}
/**
* Generate cache key for query
*/
private function generate_cache_key($query) {
return 'dbq_' . md5($query);
}
/**
* Get cache TTL based on query type
*/
private function get_cache_ttl($query) {
// Options table - cache longer
if (strpos($query, 'wp_options') !== false) {
return 3600; // 1 hour
}
// Posts table - moderate caching
if (strpos($query, 'wp_posts') !== false) {
return 300; // 5 minutes
}
// Default
return 600; // 10 minutes
}
/**
* Log cache statistics
*/
public function log_cache_stats() {
if (defined('WP_DEBUG') && WP_DEBUG) {
$total = $this->cache_stats['hits'] + $this->cache_stats['misses'];
if ($total > 0) {
$hit_rate = ($this->cache_stats['hits'] / $total) * 100;
error_log(sprintf(
'DB Cache Stats - Hits: %d, Misses: %d, Hit Rate: %.2f%%, Writes: %d',
$this->cache_stats['hits'],
$this->cache_stats['misses'],
$hit_rate,
$this->cache_stats['writes']
));
}
}
}
}
Monitoring and Troubleshooting
Real-time Query Monitor
// Query monitoring dashboard
class DB_Monitor_Dashboard {
public function __construct() {
add_action('admin_menu', [$this, 'add_menu']);
add_action('wp_ajax_get_db_stats', [$this, 'ajax_get_stats']);
}
/**
* Add admin menu
*/
public function add_menu() {
add_management_page(
'Database Monitor',
'DB Monitor',
'manage_options',
'db-monitor',
[$this, 'render_dashboard']
);
}
/**
* Render monitoring dashboard
*/
public function render_dashboard() {
?>
<div class="wrap">
<h1>Database Performance Monitor</h1>
<div id="db-stats-container">
<div class="db-stat-box">
<h3>Current Queries</h3>
<div id="current-queries">Loading...</div>
</div>
<div class="db-stat-box">
<h3>Slow Query Log</h3>
<div id="slow-queries">Loading...</div>
</div>
<div class="db-stat-box">
<h3>Table Statistics</h3>
<div id="table-stats">Loading...</div>
</div>
<div class="db-stat-box">
<h3>Index Usage</h3>
<div id="index-usage">Loading...</div>
</div>
</div>
<script>
jQuery(document).ready(function($) {
function loadDBStats() {
$.ajax({
url: ajaxurl,
data: {
action: 'get_db_stats'
},
success: function(response) {
$('#current-queries').html(response.current_queries);
$('#slow-queries').html(response.slow_queries);
$('#table-stats').html(response.table_stats);
$('#index-usage').html(response.index_usage);
}
});
}
// Load initially
loadDBStats();
// Refresh every 5 seconds
setInterval(loadDBStats, 5000);
});
</script>
<style>
.db-stat-box {
background: #fff;
border: 1px solid #ccc;
padding: 15px;
margin: 10px 0;
box-shadow: 0 1px 3px rgba(0,0,0,0.1);
}
.db-stat-box h3 {
margin-top: 0;
}
.query-info {
background: #f5f5f5;
padding: 10px;
margin: 5px 0;
font-family: monospace;
font-size: 12px;
}
.slow-query {
background: #ffebee;
}
</style>
</div>
<?php
}
/**
* Get database statistics via AJAX
*/
public function ajax_get_stats() {
global $wpdb;
$response = [
'current_queries' => $this->get_current_queries(),
'slow_queries' => $this->get_slow_queries(),
'table_stats' => $this->get_table_stats(),
'index_usage' => $this->get_index_usage()
];
wp_send_json($response);
}
/**
* Get currently running queries
*/
private function get_current_queries() {
global $wpdb;
$processes = $wpdb->get_results("SHOW PROCESSLIST");
$html = '<table class="widefat">';
$html .= '<thead><tr><th>ID</th><th>User</th><th>Time</th><th>State</th><th>Query</th></tr></thead>';
$html .= '<tbody>';
foreach ($processes as $process) {
if ($process->Command === 'Query' && $process->Info !== 'SHOW PROCESSLIST') {
$html .= '<tr>';
$html .= '<td>' . esc_html($process->Id) . '</td>';
$html .= '<td>' . esc_html($process->User) . '</td>';
$html .= '<td>' . esc_html($process->Time) . 's</td>';
$html .= '<td>' . esc_html($process->State) . '</td>';
$html .= '<td><code>' . esc_html(substr($process->Info, 0, 100)) . '</code></td>';
$html .= '</tr>';
}
}
$html .= '</tbody></table>';
return $html;
}
/**
* Get slow queries
*/
private function get_slow_queries() {
$slow_queries = get_transient('db_slow_queries');
if (empty($slow_queries)) {
return '<p>No slow queries recorded.</p>';
}
$html = '<div class="slow-queries-list">';
foreach ($slow_queries as $query) {
$html .= '<div class="query-info slow-query">';
$html .= '<strong>Time:</strong> ' . esc_html($query['time']) . 's<br>';
$html .= '<strong>Query:</strong> <code>' . esc_html($query['sql']) . '</code><br>';
$html .= '<strong>Caller:</strong> ' . esc_html($query['trace']);
$html .= '</div>';
}
$html .= '</div>';
return $html;
}
/**
* Get table statistics
*/
private function get_table_stats() {
global $wpdb;
$tables = $wpdb->get_results("
SELECT
table_name,
table_rows,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
ROUND((index_length / 1024 / 1024), 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND table_name LIKE '{$wpdb->prefix}%'
ORDER BY size_mb DESC
LIMIT 10
");
$html = '<table class="widefat">';
$html .= '<thead><tr><th>Table</th><th>Rows</th><th>Size (MB)</th><th>Index (MB)</th></tr></thead>';
$html .= '<tbody>';
foreach ($tables as $table) {
$html .= '<tr>';
$html .= '<td>' . esc_html($table->table_name) . '</td>';
$html .= '<td>' . number_format($table->table_rows) . '</td>';
$html .= '<td>' . esc_html($table->size_mb) . '</td>';
$html .= '<td>' . esc_html($table->index_mb) . '</td>';
$html .= '</tr>';
}
$html .= '</tbody></table>';
return $html;
}
/**
* Get index usage statistics
*/
private function get_index_usage() {
global $wpdb;
// Get unused indexes
$unused_indexes = $wpdb->get_results("
SELECT
s.table_name,
s.index_name,
s.cardinality
FROM information_schema.statistics s
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage u
ON s.table_schema = u.object_schema
AND s.table_name = u.object_name
AND s.index_name = u.index_name
WHERE s.table_schema = DATABASE()
AND s.table_name LIKE '{$wpdb->prefix}%'
AND s.index_name != 'PRIMARY'
AND u.count_star IS NULL
");
if (empty($unused_indexes)) {
return '<p>All indexes are being used efficiently.</p>';
}
$html = '<div class="unused-indexes">';
$html .= '<h4>Potentially Unused Indexes:</h4>';
$html .= '<ul>';
foreach ($unused_indexes as $index) {
$html .= '<li>' . esc_html($index->table_name) . '.' . esc_html($index->index_name) . '</li>';
}
$html .= '</ul>';
$html .= '</div>';
return $html;
}
}
// Initialize monitor
if (is_admin()) {
new DB_Monitor_Dashboard();
}
Advanced Optimization Techniques
Query Rewriting
// Query optimization through rewriting
class Query_Rewriter {
public function __construct() {
add_filter('posts_request', [$this, 'optimize_post_queries'], 10, 2);
add_filter('posts_clauses', [$this, 'optimize_query_clauses'], 10, 2);
}
/**
* Optimize post queries
*/
public function optimize_post_queries($query, $wp_query) {
// Remove SQL_CALC_FOUND_ROWS when not needed
if ($wp_query->get('no_found_rows')) {
$query = str_replace('SQL_CALC_FOUND_ROWS', '', $query);
}
// Use STRAIGHT_JOIN for complex queries
if ($this->is_complex_query($query)) {
$query = str_replace('SELECT', 'SELECT STRAIGHT_JOIN', $query);
}
return $query;
}
/**
* Optimize query clauses
*/
public function optimize_query_clauses($clauses, $wp_query) {
// Optimize meta queries
if (!empty($wp_query->meta_query->queries)) {
$clauses = $this->optimize_meta_queries($clauses, $wp_query);
}
// Optimize tax queries
if (!empty($wp_query->tax_query->queries)) {
$clauses = $this->optimize_tax_queries($clauses, $wp_query);
}
return $clauses;
}
/**
* Optimize meta queries
*/
private function optimize_meta_queries($clauses, $wp_query) {
// Force index usage for meta queries
if (strpos($clauses['join'], 'wp_postmeta') !== false) {
$clauses['join'] = str_replace(
'wp_postmeta',
'wp_postmeta USE INDEX (meta_key)',
$clauses['join']
);
}
return $clauses;
}
/**
* Check if query is complex
*/
private function is_complex_query($query) {
$join_count = substr_count($query, 'JOIN');
$where_count = substr_count($query, 'WHERE');
return ($join_count > 3 || $where_count > 5);
}
}
Best Practices
Database Optimization Checklist
## Query Optimization
- [ ] Use WP_Query parameters efficiently
- [ ] Avoid queries in loops
- [ ] Cache expensive queries
- [ ] Use proper pagination
- [ ] Minimize use of meta_query
- [ ] Leverage post__in when possible
## Indexing
- [ ] Add indexes for frequently queried columns
- [ ] Remove duplicate/redundant indexes
- [ ] Monitor index usage
- [ ] Keep indexes updated with ANALYZE TABLE
- [ ] Use composite indexes for multi-column queries
## Maintenance
- [ ] Schedule regular cleanup tasks
- [ ] Monitor autoloaded options size
- [ ] Clean post revisions periodically
- [ ] Remove orphaned metadata
- [ ] Optimize tables monthly
- [ ] Monitor table fragmentation
## Performance
- [ ] Enable query caching
- [ ] Use persistent object caching
- [ ] Implement read/write splitting for scale
- [ ] Monitor slow query log
- [ ] Profile expensive operations
- [ ] Set appropriate MySQL variables
## Monitoring
- [ ] Track query performance metrics
- [ ] Monitor table growth
- [ ] Watch for lock contention
- [ ] Check replication lag (if applicable)
- [ ] Set up alerts for issues
Conclusion
Database optimization is crucial for WordPress performance and requires ongoing attention. By implementing the techniques in this guide:
- Query optimization can reduce execution time by 50-90%
- Proper indexing speeds up searches by 10-100x
- Regular maintenance prevents performance degradation
- Caching strategies eliminate redundant queries
- Monitoring tools help identify issues before they impact users
Remember that database optimization is an iterative process. Start with the basics—analyze slow queries, add appropriate indexes, and implement regular maintenance. Then progressively add advanced techniques based on your specific needs.
The key is to measure, optimize, and monitor continuously. A well-optimized database is the foundation of a fast WordPress site that can scale with your growth.
Continue your WordPress optimization journey with our complete WordPress Performance Optimization Guide for more advanced techniques.