WordPress Performance: Database Optimisation Techniques

9 minute read WordPress Performance · Part 2

Learn how to optimise your WordPress database with indexing, query performance tweaks, cleanup routines, and scalability best practices.

The WordPress database is the heartbeat of your site. Left unchecked, it can become bloated, slow, and fragile.

This guide outlines technical methods to keep your MySQL (or MariaDB) database fast and lean.

1. Analyse Slow Queries

Enable query logging to find slow SQL statements:

SET global slow_query_log = 1;
SET global long_query_time = 1;

Use tools like:

  • Query Monitor (plugin)
  • New Relic APM
  • mysqladmin processlist

2. Add Custom Indexes

WordPress tables are often under-indexed. Example:

ALTER TABLE wp_postmeta ADD INDEX meta_key_idx(meta_key);

Useful for:

  • Meta queries (ACF-heavy builds)
  • WooCommerce filters
  • Custom post types with tax/meta filters

3. Clean Up Orphaned Data

Over time, plugins leave traces behind:

  • Post revisions
  • Orphaned meta rows
  • Expired transients

Use:

  • WP-Optimize
  • Custom queries:
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);

4. Optimise Autoloaded Options

Check oversized options table:

SELECT option_name, LENGTH(option_value) AS size FROM wp_options WHERE autoload = 'yes' ORDER BY size DESC LIMIT 10;

Audit for:

  • Large plugin caches (disable autoload)
  • Option bloat (move to transients or custom tables)

5. Archive Old Content

For content-heavy sites:

  • Move old posts to archive CPT
  • Store historical data in separate table
  • Disable comments on old posts to reduce lookup

6. Use InnoDB & Query Buffering

Ensure all tables use InnoDB engine:

ALTER TABLE wp_posts ENGINE=InnoDB;

Set buffer pool size in my.cnf:

innodb_buffer_pool_size=512M
innodb_log_file_size=256M

Note: query_cache was removed in MySQL 8.0. For query caching, use application-level caching with Redis or Memcached instead.

7. Avoid SELECT * in Custom Code

Retrieve only what’s needed:

$results = $wpdb->get_results("SELECT ID, post_title FROM $wpdb->posts WHERE post_type = 'page'");

Helps reduce memory and improve processing speed.

Real-World Example

In a performance retainer project:

  • Reduced wp_options from 8MB to 600KB
  • Indexed meta_key for 300k-row postmeta table
  • Created archive table for legacy press posts