Advanced MySQL Optimization in WordPress: Techniques to Reduce Load Times

Common Database Issues in WordPress

MySQL databases in WordPress accumulate redundant data and inefficient queries, leading to slowdowns. According to Kinsta, 60% of slow sites have unindexed tables or poorly designed queries.

Orphaned Tables

Uninstalled plugins or deleted themes leave leftover data in the database. Tools like Advanced Database Cleaner identify and remove these tables.

Reviewing Posts and Transients

• • Autosave revisions: WordPress stores unnecessary revisions. Limit settings in wp-config.php:

define('WP_POST_REVISIONS', 3);

• Transients: Temporary data that isn't purged. Use wp transient delete-expired via WP-CLI.

SQL Optimization Techniques

Strategic Indexing

Table	Field to Index
wp_posts	post_type, post_status
wp_options	option_name

Optimized Query for WooCommerce

SELECT * FROM wp_posts 
WHERE post_type = 'product' 
AND post_status = 'publish' 
ORDER BY post_date DESC 
LIMIT 100;

According to MySQL Tutorial, adding indexes reduces search time by 90%.

Frequently Asked Questions

How can I identify slow queries?

Enable SAVEQUERIES in wp-config.php and check the global array $wpdb->queries.

How often should the database be cleaned?

Schedule monthly tasks with WP-CLI or plugins like WP-Optimize.

Is it safe to manually delete transients?

Yes, but verify they aren't being used by active plugins. Use get_transient() for debugging.