Database Optimization Tips for Faster WordPress Sites

1. Pruning the Bloat: Post Revisions and Metadata

WordPress saves every draft and update as a full post row in wp_posts. Over a year, a 500-post site can easily have 5,000 redundant rows.

• The Fix: Define a limit in wp-config.php: define('WP_POST_REVISIONS', 5);. Use SQL to delete existing ones: DELETE FROM wp_posts WHERE post_type = 'revision';
• Orphaned Metadata: When you delete a plugin, its data often stays in wp_postmeta. Periodically run a cleanup query to find meta keys that no longer have an associated post.

2. The wp_options Audit

The wp_options table is the most common cause of high TTFB. WordPress loads every row where autoload = 'yes' on every single page load.

• The Threshold: If your autoloaded data is over 1MB, performance will tank.
• The Query: Find your biggest offenders: SELECT option_name, length(option_value) AS option_value_length FROM wp_options WHERE autoload = 'yes' ORDER BY option_value_length DESC LIMIT 10;
• The Action: If a plugin’s settings don’t need to be global, switch autoload to no.

3. Converting to InnoDB

Older WordPress databases might still be using the MyISAM engine. MyISAM uses “table-level locking,” meaning if one process is writing to a table, all other queries must wait.

• The Upgrade: Switch to InnoDB, which uses “row-level locking.” This allows multiple concurrent reads and writes, which is essential for high-traffic sites and WooCommerce.

4. Database Indexing

Indexing is like an “index” at the back of a book; it allows MySQL to find data without scanning every single row.

• The Problem: WordPress does not index the autoload column in wp_options by default.
• The Pro Tip: Manually add an index to the autoload column. This can significantly speed up the initial boot time of your application.

5. Managing Transients

Transients are a great way to cache API calls or complex queries in the database, but they can pile up if they don’t expire correctly.

• Object Caching: If you use Redis or Memcached, WordPress automatically moves transients out of the database and into RAM. This is the single biggest “win” for database performance.