Database Optimization

We start with a health check. We record the database size, the size of each table, the number of rows in key tables such as posts, postmeta, options, comments and any plugin tables, the server and database versions, the character set and storage engine, and the site’s response times for typical pages. We use a query monitor to find slow queries on the front end and in the admin area. We list the active plugins, because many problems come from one or two heavy plugins, such as analytics, security logs, form entries, page builders, related-post tools or WooCommerce extensions. Before anything else, we take a full database backup and confirm that it can be restored; see website backups and restoration. For larger or business-critical sites, we copy the site to staging and run the work there first. We then clean safely in stages. We limit the number of revisions stored per post, and remove old revisions beyond a reasonable number. We empty trashed posts and comments after confirming with you, remove spam and unapproved comments if you agree, and delete auto-drafts. We clear expired transients and, where the site has an object cache, review how transients are stored. We look at the options table for autoloaded data. Large autoloaded rows, often left by plugins, are read on every request, so we find the biggest ones, identify which plugin they belong to and, where it is safe, turn off autoloading or remove data of plugins that are no longer used. We check for orphaned postmeta, termmeta and usermeta rows with no matching parent, and remove them in small batches. We identify tables left by uninstalled plugins and list them for approval before dropping. We examine WooCommerce and similar systems for old sessions, expired carts, action scheduler logs and old logs, which can reach millions of rows. We review the database engine and convert old MyISAM tables to InnoDB where appropriate, which improves reliability and concurrency. We then optimise and repair tables to reclaim space and refresh statistics, and consider adding indexes for slow queries on large postmeta or custom tables, only when testing shows a clear gain and the change is reversible. We also fix configuration issues such as scheduled tasks that are failing, or plugins that write logs without limit. After cleaning, we retest the site: front-end pages, search, forms, checkout, the admin area and scheduled jobs. We compare database size and response times with the baseline and note the improvement. To keep things tidy, we set limits for revisions in the site configuration, schedule light monthly clean-ups, and recommend a review when major plugins are added; see website maintenance plans. Where the server itself is the limit, we advise on upgrading; see WordPress hosting setup. We cannot recover speed that is lost to slow hosting, heavy themes or large images, and database cleaning will not fix them, but removing bloat often brings visible gains, especially in older sites and shops.