Subtle diagonal pinstripe pattern in light grey and cream, giving an impression of printed editorial paper

Tune InnoDB for a faster WordPress site

WordPress speed is often treated as a plugin, theme, or image problem, while the database quietly serves navigation, product data, user accounts, and cached settings on every request. A slow query or poorly sized InnoDB buffer can therefore affect the whole site. Tuning the database still needs a measured workload because additional CPU work, external calls, and full-page caching can hide or imitate database pressure.

Good tuning follows a repeatable sequence: establish which queries consume time during representative traffic, preserve a restorable backup, change one setting at a time, and compare latency, throughput, CPU, disk I/O, and error rates. A copied my.cnf from another host can reduce performance or durability, especially on managed Australian hosting where the database service may not expose the same controls as a dedicated VPS.

Area InnoDB MyISAM
Concurrency Row-level locking where appropriate and MVCC Table-level locking
Transactions Atomic transactions and rollback No transactions
Recovery Redo log, undo handling, and crash recovery Manual repair after some failures
Storage Data and indexes can share files Separate data and index files
WordPress fit Preferred default for current sites Mainly encountered in legacy or plugin data

Begin with the database you actually have

Optimizing MySQL tables for WordPress performance with InnoDB begins with inventory, not blind conversion. Current WordPress core normally uses InnoDB, yet an older installation or a third-party plugin may still create MyISAM objects. Check engines, approximate sizes, row formats, collations, and indexes before deciding what deserves attention.

SELECT TABLE_NAME, ENGINE, TABLE_ROWS,
       DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;

For InnoDB, TABLE_ROWS is an estimate rather than an exact count. A large wp_postmeta or wp_options object may be normal for the content and traffic, while a smaller object with poor indexes can cause more trouble. Confirm storage engines in WP-CLI with wp db query, inspect plugin migrations, and test any legacy FULLTEXT or archive requirements before moving it to InnoDB.

Measure queries before changing settings

Profile a representative page load before editing configuration. Group WordPress traffic by operation: frontend URLs, admin screens, AJAX calls, REST requests, cron, and WooCommerce checkout activity. Record database CPU, disk latency, buffer-pool reads, temporary-disk use, and the slowest endpoints. This separates genuine query cost from PHP object creation or a remote payment service.

The slow query log and Performance Schema provide useful evidence on MySQL 5.7 and 8.0. Enable long-query logging temporarily, keep thresholds high enough to avoid constant disk writes, and remove it when investigation ends. On shared hosting, ask the provider for query statistics because changing global settings may be unavailable. Review peaks as well as averages; an occasional background job can create severe latency for checkout or logged-in administration.

Run EXPLAIN on suspect statements and inspect join type, chosen indexes, estimated versus actual rows, sorting, and temporary structures. EXPLAIN ANALYZE can execute the statement, so reserve it for staging or a carefully bounded production query. Capture the same baseline after cache warming because cold and warm measurements answer different performance questions.

Size InnoDB for the hosting model

On a dedicated database server, the InnoDB buffer pool often receives roughly 50–70% of available memory, subject to the workload and how much the host needs for connections, sorting, and operating-system cache. On a small VPS running PHP and MySQL together, that percentage can starve the application. Many managed Australian WordPress plans run MariaDB or MySQL behind fixed resource limits, making provider metrics and support advice more reliable than a hand-edited configuration.

SHOW VARIABLES LIKE 'innodb%';

Modern MySQL 8.0.30 and later use innodb_redo_log_capacity; older MySQL releases and MariaDB use different sizing controls, including innodb_log_file_size. A larger redo area can smooth write bursts but consumes disk space and may lengthen recovery. The buffer pool should hold as much of the active data and index set as practical, yet an oversized pool on a combined application and database host can cause swapping.

Keep innodb_flush_log_at_trx_commit set to 1 when transaction durability is essential. Values of 2 or 0 reduce some flush overhead but can lose committed transactions after an operating-system or database failure. Also review sync_binlog when binary logging is enabled. These are deliberate durability trade-offs rather than routine speed switches, and WooCommerce order or membership changes deserve particular caution.

Repair and optimise tables without surprises

Create and restore a backup before maintenance rather than merely exporting one. Test the procedure on staging with production-like rows, then confirm free disk space, replication state, and the acceptable recovery point. Schedule work during a quiet trading period and provide a written rollback path. High-traffic stores may need maintenance windows outside Sydney business hours or an agreed period with the business team.

Use maintenance commands for a defined purpose. ANALYZE TABLE updates optimiser statistics but does not compact data. OPTIMIZE TABLE rebuilds an InnoDB object to reorganise its pages, consumes CPU and I/O, and can wait for metadata locks. Fragmentation and statistics matter when execution plans have regressed, but a fixed monthly rebuild of every object creates needless load.

ANALYZE TABLE wp_posts;
OPTIMIZE TABLE wp_posts;

Adapt the names to the site’s database prefix and run statements individually so any failure is clear. Conversion from MyISAM with ALTER TABLE ... ENGINE=InnoDB copies data and can block writes on a large object. For substantial wp_posts, wp_postmeta, logging, or analytics datasets, rehearse the migration and consider pt-online-schema-change or gh-ost; both have prerequisites that ordinary shared hosting may not satisfy.

Make indexes match real WordPress queries

WordPress already defines indexes for common access paths, and adding one to every column can slow inserts and revisions without improving any query. Start with EXPLAIN for slow post, user, option, taxonomy, and WooCommerce lookups. Look for full scans on selective columns, unstable join order, and filesort operations caused by a missing composite index rather than insufficient memory.

Index column order follows the query shape. Equality filters generally precede range filters in a useful composite index, while sorting and covering columns depend on the exact WHERE, ORDER BY, and selected fields. A targeted composite index for wp_postmeta may help a fixed access pattern, but meta_value can be a long text field and any prefix must match real filters and collations. Existing meta_key indexes may already be sufficient.

Remove only indexes proven redundant or unused over a representative period; do not delete PRIMARY, unique keys, or FULLTEXT indexes by guesswork. Large writes can make every extra index costly, especially during bursts of revisions, orders, and Action Scheduler jobs. When application code repeatedly generates a predictable query that still scans a large dataset, a carefully chosen index can be the most durable fix.

Control growth in WordPress data

Bloat in post revisions, spam comments, expired transients, analytics events, and plugin logs enlarges both data and indexes. Inspect row counts and age distributions, then apply a documented retention policy. wp post list --post_type=revision --format=count and targeted SQL can establish the baseline. Retention should match recovery, editorial, legal, and WooCommerce requirements rather than a blanket policy of deleting old records.

define( 'WP_POST_REVISIONS', 5 );

A smaller revision limit changes editorial behaviour, so it belongs in version-controlled configuration and should be tested with the people managing content. Purge known-expired transients, failed WooCommerce sessions, and plugin debug logs through maintained tools; some transients exist only in a persistent object cache and will not appear in MySQL. Never remove active scheduled actions or completed orders merely to improve disk-usage figures.

Review cron frequency and plugin cleanup jobs as carefully as frontend queries. Duplicate scheduled events can repeatedly touch the same records. A persistent Redis or Memcached cache can absorb repeated option and metadata reads, but it requires sensible invalidation, memory limits, and monitoring. Database tuning then has less pressure to compensate for workloads that belong in the application cache.

Validate performance and Australian operational needs

Repeat the same load test after each change, using warmed and cold cache states plus real WordPress journeys: a search, account update, media request, and WooCommerce checkout. Compare median and 95th-percentile response times, queries per request, database CPU, I/O latency, buffer-pool hit rate, slow-query count, PHP memory, and error rates. A faster query that increases checkout errors or cache invalidation has not improved the service.

Consider geography as part of the measurement. A managed service with data in Sydney or Melbourne may suit Australian data-residency commitments, yet users in Brisbane or Perth can still experience meaningful network latency. Test primary and disaster-recovery regions, DNS failover, TLS handshakes, and remote backups separately. Australian managed WordPress providers may offer local support and regional controls, but the service agreement must define where replicas and backups are actually stored.

The WordPress database may contain names, email addresses, order histories, and account identifiers. For organisations covered by the Privacy Act 1988, database and backup handling should support the Australian Privacy Principles and Notifiable Data Breaches process. Encrypted backups, least-privilege database accounts, tested restores, and ACSC Essential Eight risk-management practices reduce operational exposure. Record every configuration change with its owner, reason, baseline, result, and rollback method so the resulting setup remains measured, documented, and reversible.