WordPress database optimisation improves site performance through efficient queries, proper indexing, and regular maintenance. Databases store all WordPress content, settings, and user data. Professional optimisation reduces query times, decreases server load, and accelerates page rendering enhancing user experience.
WordPress uses MySQL or MariaDB storing content in relational tables. Understanding structure enables effective optimisation.
Core Tables:
wp_posts - Posts, pages, custom post types
wp_postmeta - Post metadata
wp_users - User accounts
wp_usermeta - User metadata
wp_comments - Comments
wp_commentmeta - Comment metadata
wp_terms - Categories, tags, taxonomy terms
wp_term_taxonomy - Term relationships
wp_term_relationships - Content-term associations
wp_options - Site settings
wp_links - Blogroll links (legacy)
wp_posts table contains most content. wp_postmeta stores custom fields creating flexible data storage.
wp_options table stores site configuration. Large wp_options tables slow sites significantly.
Understanding table purposes enables targeted optimisation identifying performance bottlenecks.
Professional WordPress development requires database proficiency optimising data layer effectively.
Efficient queries retrieve data quickly minimising server resources. Poor queries slow every page load.
Use WP_Query Efficiently:
// Efficient - only retrieve needed fields
$args = array(
'posts_per_page' => 10,
'fields' => 'ids', // Only get post IDs
'no_found_rows' => true, // Skip pagination count
'update_post_meta_cache' => false, // Skip meta cache
'update_post_term_cache' => false, // Skip term cache
);
$query = new WP_Query($args);
Avoid Expensive Operations:
// Inefficient - retrieves all posts
$all_posts = get_posts(array('numberposts' => -1));
// Efficient - limit results
$recent_posts = get_posts(array('numberposts' => 10));
Reduce Meta Queries:
// Inefficient - meta query
$args = array(
'meta_query' => array(
array('key' => 'color', 'value' => 'red')
)
);
// Efficient - taxonomy when possible
$args = array(
'tax_query' => array(
array('taxonomy' => 'color', 'terms' => 'red')
)
);
Monitor Queries:
Use Query Monitor plugin identifying slow queries. Profile queries during development optimising before production.
Efficient queries dramatically improve performance especially on content-heavy sites.
Indexes accelerate data retrieval. WordPress includes default indexes; custom post types and meta keys benefit from additional indexes.
Check Existing Indexes:
SHOW INDEX FROM wp_posts;
SHOW INDEX FROM wp_postmeta;
Add Custom Indexes:
global $wpdb;
// Index custom post type
$wpdb->query("
CREATE INDEX post_type_status_date
ON {$wpdb->posts}(post_type, post_status, post_date)
");
// Index custom meta key
$wpdb->query("
CREATE INDEX meta_key_value
ON {$wpdb->postmeta}(meta_key, meta_value(10))
");
Strategic Indexing:
Index columns used in WHERE clauses and JOIN conditions. Over-indexing slows INSERT/UPDATE operations.
Monitor slow query logs identifying unindexed columns causing performance issues.
Composite Indexes:
Index multiple columns together for combined queries:
$wpdb->query("
CREATE INDEX type_status
ON {$wpdb->posts}(post_type, post_status)
");
Proper indexing provides substantial performance improvements for large databases.
WordPress databases accumulate unnecessary data over time. Regular cleanup improves performance and reduces size.
Remove Post Revisions:
DELETE FROM wp_posts WHERE post_type = 'revision';
Limit future revisions in wp-config.php:
define('WP_POST_REVISIONS', 5);
Delete Auto-Drafts:
DELETE FROM wp_posts WHERE post_status = 'auto-draft';
Clean Trashed Posts:
DELETE FROM wp_posts WHERE post_status = 'trash';
Remove Orphaned Metadata:
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;
Clear Transients:
DELETE FROM wp_options
WHERE option_name LIKE '_transient_%';
Delete Spam Comments:
DELETE FROM wp_comments WHERE comment_approved = 'spam';
Cleanup Plugins:
WP-Optimize, WP-Sweep, Advanced Database Cleaner automate cleanup safely.
Regular maintenance prevents database bloat maintaining optimal performance.
Combine database cleanup with WordPress speed optimisation comprehensive performance improvements.
wp_options table stores site configuration. Autoloaded options load every page. Large wp_options tables significantly slow sites.
Identify Autoloaded Options:
SELECT SUM(LENGTH(option_value)) as autoload_size
FROM wp_options
WHERE autoload = 'yes';
Target under 1MB autoloaded data. Larger sizes slow every request.
Find Large Options:
SELECT option_name, LENGTH(option_value) as size
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size DESC
LIMIT 20;
Disable Autoloading:
UPDATE wp_options
SET autoload = 'no'
WHERE option_name = 'large_option_name';
Clean Orphaned Options:
Deactivated plugins often leave options. Identify and remove safely:
SELECT * FROM wp_options
WHERE option_name LIKE '%plugin_name%';
Monitor wp_options size regularly. Growing options tables indicate cleanup needs.
MySQL table optimisation defragments tables recovering wasted space and improving query performance.
Optimise Tables:
OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments;
Check Table Status:
SHOW TABLE STATUS WHERE Name LIKE 'wp_%';
Look at Data_free column showing reclaimable space.
Repair Corrupted Tables:
REPAIR TABLE wp_posts;
Convert MyISAM to InnoDB:
ALTER TABLE wp_posts ENGINE=InnoDB;
InnoDB provides better performance for WordPress workloads.
Scheduled Optimisation:
Automate monthly optimisation through cron jobs or plugins. WP-Optimize schedules automatic maintenance.
Regular optimisation maintains database efficiency preventing performance degradation.
Advanced techniques optimise complex queries reducing database load.
Direct Database Queries:
global $wpdb;
// Prepared statement
$posts = $wpdb->get_results($wpdb->prepare(
"SELECT ID, post_title FROM {$wpdb->posts}
WHERE post_type = %s
AND post_status = %s
LIMIT %d",
'post', 'publish', 10
));
JOIN Optimisation:
$results = $wpdb->get_results("
SELECT p.ID, p.post_title, pm.meta_value
FROM {$wpdb->posts} p
INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
WHERE pm.meta_key = 'featured'
AND pm.meta_value = '1'
LIMIT 10
");
Subquery Optimisation:
$results = $wpdb->get_results("
SELECT * FROM {$wpdb->posts}
WHERE ID IN (
SELECT post_id FROM {$wpdb->postmeta}
WHERE meta_key = 'category' AND meta_value = 'featured'
)
LIMIT 10
");
Caching Query Results:
$cache_key = 'custom_query_results';
$results = wp_cache_get($cache_key);
if (false === $results) {
global $wpdb;
$results = $wpdb->get_results("SELECT...");
wp_cache_set($cache_key, $results, '', 3600);
}
Advanced techniques require SQL proficiency. Test thoroughly ensuring security and correctness.
Database caching reduces query execution frequency dramatically improving performance.
Object Caching:
Persistent object caches (Redis, Memcached) store query results:
// Install Redis or Memcached object cache plugin
// Caching happens automatically
$posts = get_posts(array('numberposts' => 10));
Query Results Caching:
$cache_key = md5('custom_query');
$results = get_transient($cache_key);
if (false === $results) {
global $wpdb;
$results = $wpdb->get_results("SELECT...");
set_transient($cache_key, $results, HOUR_IN_SECONDS);
}
Fragment Caching:
Cache expensive output:
$cache_key = 'widget_output';
$output = get_transient($cache_key);
if (false === $output) {
ob_start();
// Generate expensive output
$output = ob_get_clean();
set_transient($cache_key, $output, DAY_IN_SECONDS);
}
echo $output;
Cache Invalidation:
Clear caches when content updates:
function clear_custom_caches($post_id) {
delete_transient('custom_query');
wp_cache_flush();
}
add_action('save_post', 'clear_custom_caches');
Caching provides massive performance improvements. Implement caching layers strategically.
Continuous monitoring identifies performance degradation enabling proactive optimisation.
Query Monitor Plugin:
Essential tool displaying:
MySQL Slow Query Log:
Enable in my.cnf:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
Review slow queries regularly optimising problematic queries.
Performance Schema:
MySQL Performance Schema provides detailed metrics:
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
New Relic / Scout APM:
Professional monitoring tools providing comprehensive database insights.
Regular Audits:
Schedule quarterly database audits:
Proactive monitoring prevents performance crises identifying issues early.
How often should I optimise WordPress database?
Optimise WordPress databases monthly for active sites, quarterly for static sites. High-traffic sites benefit from weekly optimisation. Schedule automatic optimisation through plugins like WP-Optimize running maintenance during low-traffic periods. Monitor database size and query performance determining optimisation frequency. Growing databases or degrading performance indicate more frequent optimisation needed. Combine optimisation with regular backups preventing data loss.
Does database optimisation improve site speed?
Yes, database optimisation significantly improves site speed by reducing query execution time, decreasing database size, and improving cache efficiency. Optimised databases respond faster to every content request. Benefits vary by site: content-heavy sites see dramatic improvements; simple sites experience modest gains. Combine database optimisation with WordPress speed optimisation for maximum performance. Measure improvements using speed testing tools validating optimisation effectiveness.
Can I optimise database without plugins?
Yes, optimise databases manually through phpMyAdmin or MySQL command line. Run OPTIMIZE TABLE commands, delete unnecessary data, and review indexes manually. However, plugins like WP-Optimise simplify maintenance automating scheduled optimisation, providing safety checks, and offering user-friendly interfaces. Manual optimisation requires database expertise risking data loss if mistakes occur. Plugins prove safer for most users whilst manual optimisation suits experienced developers requiring precise control.
What causes WordPress database bloat?
WordPress database bloat accumulates from post revisions, auto-drafts, trashed posts, spam comments, expired transients, orphaned metadata, and plugin/theme leftovers. Active sites with frequent updates generate significant bloat. Plugins storing temporary data without cleanup contribute. Deactivated plugins often leave options and tables. Regular content creation without maintenance expands databases unnecessarily. Limit revisions, schedule cleanup, and remove unused plugins preventing bloat accumulation.
Is it safe to delete post revisions?
Yes, delete post revisions safely though consider keeping recent revisions for content recovery. Backup databases before deletion enabling restoration if needed. Limit future revisions in wp-config.php controlling accumulation. Some users prefer keeping 3-5 recent revisions balancing recovery capability against database size. Evaluate revision importance against storage concerns. Most sites safely delete old revisions without impacting functionality.
How do I backup database before optimisation?
Backup WordPress databases through hosting control panels (cPanel, Plesk), command line mysqldump, or plugins like UpdraftPlus or BackupBuddy. Export complete databases before optimisation attempts. Store backups offsite preventing server failures affecting backups. Test backup restoration verifying backups work correctly. Schedule automatic backups providing recovery points. Never optimise production databases without current backups preventing irreversible data loss.
Can large databases slow WordPress?
Yes, large databases slow WordPress through longer query execution times, increased memory usage, and backup/restore delays. However, properly optimized large databases perform well. Database size matters less than query efficiency, indexing, and caching. 100MB optimized database outperforms 10MB poorly-indexed database. Focus on query optimisation, proper indexes, and caching rather than size alone. Large content-rich sites require robust hosting handling database demands.
Related WordPress Development Topics:
Written by the WordPress Development Team at London Web Design, optimising databases for London businesses since 2010.
