{"id":3385,"date":"2025-12-26T15:09:33","date_gmt":"2025-12-26T12:09:33","guid":{"rendered":"https:\/\/www.dchost.com\/blog\/wordpress-database-optimization-guide-wp_options-autoload-and-table-bloat\/"},"modified":"2025-12-26T15:09:33","modified_gmt":"2025-12-26T12:09:33","slug":"wordpress-database-optimization-guide-wp_options-autoload-and-table-bloat","status":"publish","type":"post","link":"https:\/\/www.dchost.com\/blog\/en\/wordpress-database-optimization-guide-wp_options-autoload-and-table-bloat\/","title":{"rendered":"WordPress Database Optimization Guide: wp_options, Autoload and Table Bloat"},"content":{"rendered":"<div class=\"dchost-blog-content-wrapper\"><p>Many slow WordPress sites do not have a PHP or caching problem at all \u2013 the real bottleneck sits quietly inside MySQL. A few megabytes of forgotten options, transients that never expire, or a single oversized table can be enough to push your Time To First Byte (TTFB) up, increase CPU\/IO load on your hosting, and make every admin click feel heavier. In this guide, we will look specifically at the parts of the WordPress database that most often cause trouble: the <strong>wp_options<\/strong> table, the <strong>autoload<\/strong> flag, and oversized tables such as <strong>wp_postmeta<\/strong>, logs and statistics. As the dchost.com team, we see the same patterns across hundreds of sites on shared hosting, <a href=\"https:\/\/www.dchost.com\/vps\">VPS<\/a> and <a href=\"https:\/\/www.dchost.com\/dedicated-server\">dedicated server<\/a>s. The good news: with a structured approach and a few simple SQL queries, you can clean up years of bloat, reduce query times, and delay (or at least justify) your next hosting upgrade. Let\u2019s walk through what to check, what to clean, and how to keep it healthy in the long term.<\/p>\n<div id=\"toc_container\" class=\"toc_transparent no_bullets\"><p class=\"toc_title\">\u0130&ccedil;indekiler<\/p><ul class=\"toc_list\"><li><a href=\"#How_WordPress_Uses_MySQL_And_Why_It_Gets_Bloated\"><span class=\"toc_number toc_depth_1\">1<\/span> How WordPress Uses MySQL (And Why It Gets Bloated)<\/a><\/li><li><a href=\"#Diagnosing_WordPress_Database_Performance_Problems\"><span class=\"toc_number toc_depth_1\">2<\/span> Diagnosing WordPress Database Performance Problems<\/a><ul><li><a href=\"#1_Check_Overall_Database_and_Table_Sizes\"><span class=\"toc_number toc_depth_2\">2.1<\/span> 1. Check Overall Database and Table Sizes<\/a><\/li><li><a href=\"#2_Inspect_wp_options_and_Autoload_Size\"><span class=\"toc_number toc_depth_2\">2.2<\/span> 2. Inspect wp_options and Autoload Size<\/a><\/li><li><a href=\"#3_Identify_Slow_Queries\"><span class=\"toc_number toc_depth_2\">2.3<\/span> 3. Identify Slow Queries<\/a><\/li><li><a href=\"#4_Always_Work_on_a_Staging_Copy_First\"><span class=\"toc_number toc_depth_2\">2.4<\/span> 4. Always Work on a Staging Copy First<\/a><\/li><\/ul><\/li><li><a href=\"#Fixing_wp_options_and_Autoload_Bloat\"><span class=\"toc_number toc_depth_1\">3<\/span> Fixing wp_options and Autoload Bloat<\/a><ul><li><a href=\"#1_Clean_Up_Expired_and_Stale_Transients\"><span class=\"toc_number toc_depth_2\">3.1<\/span> 1. Clean Up Expired and Stale Transients<\/a><\/li><li><a href=\"#2_Reduce_Oversized_Autoloaded_Options\"><span class=\"toc_number toc_depth_2\">3.2<\/span> 2. Reduce Oversized Autoloaded Options<\/a><\/li><li><a href=\"#3_Remove_Options_Left_Behind_by_Uninstalled_Plugins\"><span class=\"toc_number toc_depth_2\">3.3<\/span> 3. Remove Options Left Behind by Uninstalled Plugins<\/a><\/li><li><a href=\"#4_Keep_Autoload_Size_Under_Control\"><span class=\"toc_number toc_depth_2\">3.4<\/span> 4. Keep Autoload Size Under Control<\/a><\/li><\/ul><\/li><li><a href=\"#Dealing_With_Oversized_Tables_wp_postmeta_Revisions_and_Logs\"><span class=\"toc_number toc_depth_1\">4<\/span> Dealing With Oversized Tables: wp_postmeta, Revisions and Logs<\/a><ul><li><a href=\"#1_wp_postmeta_Bloat\"><span class=\"toc_number toc_depth_2\">4.1<\/span> 1. wp_postmeta Bloat<\/a><\/li><li><a href=\"#2_Cleaning_Up_Revisions_Trash_and_Spam\"><span class=\"toc_number toc_depth_2\">4.2<\/span> 2. Cleaning Up Revisions, Trash and Spam<\/a><\/li><li><a href=\"#3_Log_and_Statistic_Tables\"><span class=\"toc_number toc_depth_2\">4.3<\/span> 3. Log and Statistic Tables<\/a><\/li><li><a href=\"#4_OPTIMIZE_and_ANALYZE_After_Cleanup_With_Care\"><span class=\"toc_number toc_depth_2\">4.4<\/span> 4. OPTIMIZE and ANALYZE After Cleanup (With Care)<\/a><\/li><\/ul><\/li><li><a href=\"#Indexes_Storage_Engine_Choice_and_Other_MySQL-Level_Tweaks\"><span class=\"toc_number toc_depth_1\">5<\/span> Indexes, Storage Engine Choice and Other MySQL-Level Tweaks<\/a><ul><li><a href=\"#1_Ensure_InnoDB_Is_Used_for_Core_Tables\"><span class=\"toc_number toc_depth_2\">5.1<\/span> 1. Ensure InnoDB Is Used for Core Tables<\/a><\/li><li><a href=\"#2_Add_or_Fix_Missing_Indexes\"><span class=\"toc_number toc_depth_2\">5.2<\/span> 2. Add or Fix Missing Indexes<\/a><\/li><li><a href=\"#3_Tune_MySQL_for_Your_Hosting_Plan\"><span class=\"toc_number toc_depth_2\">5.3<\/span> 3. Tune MySQL for Your Hosting Plan<\/a><\/li><\/ul><\/li><li><a href=\"#Ongoing_Maintenance_and_Hosting-Side_Strategy\"><span class=\"toc_number toc_depth_1\">6<\/span> Ongoing Maintenance and Hosting-Side Strategy<\/a><ul><li><a href=\"#1_Automate_Regular_Cleanup\"><span class=\"toc_number toc_depth_2\">6.1<\/span> 1. Automate Regular Cleanup<\/a><\/li><li><a href=\"#2_Monitor_Growth_Over_Time\"><span class=\"toc_number toc_depth_2\">6.2<\/span> 2. Monitor Growth Over Time<\/a><\/li><li><a href=\"#3_When_Cleanup_Is_Not_Enough_Scaling_the_Database\"><span class=\"toc_number toc_depth_2\">6.3<\/span> 3. When Cleanup Is Not Enough: Scaling the Database<\/a><\/li><\/ul><\/li><li><a href=\"#Summary_and_Practical_Next_Steps\"><span class=\"toc_number toc_depth_1\">7<\/span> Summary and Practical Next Steps<\/a><\/li><\/ul><\/div>\n<h2><span id=\"How_WordPress_Uses_MySQL_And_Why_It_Gets_Bloated\">How WordPress Uses MySQL (And Why It Gets Bloated)<\/span><\/h2>\n<p>Every standard WordPress installation uses a set of core tables in a MySQL or MariaDB database. The usual suspects are:<\/p>\n<ul>\n<li><strong>wp_posts<\/strong>: posts, pages, products, revisions, custom post types<\/li>\n<li><strong>wp_postmeta<\/strong>: metadata for posts (custom fields, WooCommerce product data, SEO data, etc.)<\/li>\n<li><strong>wp_options<\/strong>: site-wide settings, plugin options, transients, cache entries<\/li>\n<li><strong>wp_users<\/strong> and <strong>wp_usermeta<\/strong>: user accounts and their metadata<\/li>\n<li><strong>wp_comments<\/strong> and <strong>wp_commentmeta<\/strong>: comments and related data<\/li>\n<li>Taxonomy-related tables: <strong>wp_terms<\/strong>, <strong>wp_term_taxonomy<\/strong>, <strong>wp_term_relationships<\/strong><\/li>\n<\/ul>\n<p>On a fresh site, all of these are small and queries are fast. Over time, three things usually cause trouble:<\/p>\n<ul>\n<li><strong>Autoload bloat in wp_options<\/strong>: too many or too large options loaded on every page request.<\/li>\n<li><strong>Unbounded growth of meta tables<\/strong>: especially <strong>wp_postmeta<\/strong> for WooCommerce stores, page builders and custom fields.<\/li>\n<li><strong>Log\/statistic tables<\/strong> created by plugins that never rotate or purge old data.<\/li>\n<\/ul>\n<p>Unlike PHP or web server tuning, these issues are often invisible until they become serious. Queries still work, but they get slower; each request needs more disk IO, CPU and RAM; and eventually your hosting plan looks \u201ctoo small\u201d even though the real fix is a database cleanup. We have already covered server-side tuning in depth in <a href='https:\/\/www.dchost.com\/blog\/en\/wordpress-icin-sunucu-tarafi-optimizasyon-php-fpm-opcache-redis-ve-mysql-ile-neyi-ne-zaman-nasil-ayarlamalisin\/'>our guide to server-side optimization for WordPress with PHP-FPM, OPcache, Redis and MySQL<\/a>. Here we will stay inside the database itself.<\/p>\n<h2><span id=\"Diagnosing_WordPress_Database_Performance_Problems\">Diagnosing WordPress Database Performance Problems<\/span><\/h2>\n<h3><span id=\"1_Check_Overall_Database_and_Table_Sizes\">1. Check Overall Database and Table Sizes<\/span><\/h3>\n<p>First, you need a quick picture of what is actually big. On most hosting panels (cPanel, DirectAdmin, Plesk), you can open phpMyAdmin and see the size of each table in your WordPress database. Sort by \u201cSize\u201d and note the top 3\u20135 tables.<\/p>\n<p>You can get a more precise view via SQL:<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">SELECT \n  TABLE_NAME, \n  ROUND((DATA_LENGTH + INDEX_LENGTH) \/ 1024 \/ 1024, 2) AS size_mb\nFROM information_schema.TABLES\nWHERE TABLE_SCHEMA = 'your_database_name'\nORDER BY size_mb DESC;\n<\/code><\/pre>\n<p>Typical patterns we see:<\/p>\n<ul>\n<li><strong>wp_postmeta<\/strong> larger than <strong>wp_posts<\/strong> by a big margin (for example 2 GB vs 300 MB).<\/li>\n<li><strong>wp_options<\/strong> several hundred MB or more.<\/li>\n<li>Custom plugin tables like <code>wp_xyz_logs<\/code>, <code>wp_xyz_stats<\/code> growing into gigabytes.<\/li>\n<\/ul>\n<p>Any table over a few hundred MB deserves a closer look, especially if you are on shared hosting or a small VPS. If your database is huge and MySQL also shows high CPU\/IO at peak hours, combine this analysis with the techniques we described in <a href='https:\/\/www.dchost.com\/blog\/en\/siteniz-belli-saatlerde-yavasliyorsa-paylasimli-hosting-ve-vpste-cpu-io-ve-mysql-darbogazi-teshisi\/'>our guide to diagnosing CPU, IO and MySQL bottlenecks when your site is slow only at certain hours<\/a>.<\/p>\n<h3><span id=\"2_Inspect_wp_options_and_Autoload_Size\">2. Inspect wp_options and Autoload Size<\/span><\/h3>\n<p>WordPress loads all rows in <strong>wp_options<\/strong> where <code>autoload = 'yes'<\/code> on every page load (front-end and usually admin). That means the total size of autoloaded options directly affects memory usage and response time.<\/p>\n<p>Run this query to see the total autoload size:<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">SELECT \n  COUNT(*) AS autoload_count,\n  ROUND(SUM(LENGTH(option_value)) \/ 1024 \/ 1024, 2) AS autoload_mb\nFROM wp_options\nWHERE autoload = 'yes';\n<\/code><\/pre>\n<p>As a rule of thumb, keeping autoloaded data under 1\u20132 MB is a good target for a typical site. We frequently see sites with 10\u201350 MB of autoload data because plugins store large arrays, cached API responses or statistics there.<\/p>\n<p>Then list the heaviest autoloaded options:<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">SELECT \n  option_name,\n  autoload,\n  ROUND(LENGTH(option_value) \/ 1024, 2) AS size_kb\nFROM wp_options\nWHERE autoload = 'yes'\nORDER BY size_kb DESC\nLIMIT 50;\n<\/code><\/pre>\n<p>Note down any single option larger than 100\u2013200 KB and any patterns (for example many <code>_transient_*<\/code> options, old plugin prefixes, etc.).<\/p>\n<h3><span id=\"3_Identify_Slow_Queries\">3. Identify Slow Queries<\/span><\/h3>\n<p>If you have access to the MySQL slow query log (common on VPS, dedicated or managed database servers), enable it and watch for queries against <strong>wp_options<\/strong> and large tables. On shared hosting, or when you prefer a WordPress-level view, plugins like Query Monitor can show which database queries are slow on each request.<\/p>\n<p>Look specifically for:<\/p>\n<ul>\n<li>SELECT queries on <strong>wp_options<\/strong> taking more than 0.1\u20130.2 seconds.<\/li>\n<li>SELECT\/UPDATE queries on <strong>wp_postmeta<\/strong> that scan many rows (high <code>Rows_examined<\/code>).<\/li>\n<li>Queries on log\/stat tables with no useful indexes.<\/li>\n<\/ul>\n<p>Database tuning and query analysis go hand in hand with a solid backup strategy. Before you touch any data, review your backup plan. We explained practical options in <a href='https:\/\/www.dchost.com\/blog\/en\/wordpress-yedekleme-stratejileri-paylasimli-hosting-ve-vpste-otomatik-yedek-ve-geri-yukleme\/'>our WordPress backup strategies guide for shared hosting and VPS<\/a> and also compared <a href='https:\/\/www.dchost.com\/blog\/en\/mysql-veritabani-yedekleme-stratejileri-mysqldump-percona-xtrabackup-ve-snapshot-nasil-secilir\/'>MySQL backup tools like mysqldump, Percona XtraBackup and snapshots<\/a> for more advanced environments.<\/p>\n<h3><span id=\"4_Always_Work_on_a_Staging_Copy_First\">4. Always Work on a Staging Copy First<\/span><\/h3>\n<p>Direct SQL changes are powerful but unforgiving. Especially if you work on a live e\u2011commerce store or a busy blog, clone your site to staging and test there first. If you are on cPanel, you can follow <a href='https:\/\/www.dchost.com\/blog\/en\/wordpress-staging-ortami-nasil-kurulur-cpanelde-alt-alan-adi-klonlama-ve-guvenli-yayina-alma\/'>our step-by-step guide for creating a WordPress staging environment on cPanel<\/a>, then run the cleanup steps safely. Once you validate that everything works, repeat the same process on production during a maintenance window.<\/p>\n<h2><span id=\"Fixing_wp_options_and_Autoload_Bloat\">Fixing wp_options and Autoload Bloat<\/span><\/h2>\n<h3><span id=\"1_Clean_Up_Expired_and_Stale_Transients\">1. Clean Up Expired and Stale Transients<\/span><\/h3>\n<p>Transients are temporary cache entries stored in <strong>wp_options<\/strong> (unless you use an external object cache like Redis). They have names like <code>_transient_*<\/code> and <code>_site_transient_*<\/code>. Normally, WordPress cleans them up over time, but bugs or plugin issues can leave many behind.<\/p>\n<p>List how many transient rows you have:<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">SELECT COUNT(*) AS transient_count\nFROM wp_options\nWHERE option_name LIKE '\\_transient\\_%' \n   OR option_name LIKE '\\_site_transient\\_%';\n<\/code><\/pre>\n<p>On a medium site, a few hundred transients are normal. Tens of thousands are a red flag.<\/p>\n<p>To delete <strong>expired<\/strong> transients safely, use WordPress functions (wp-cli is ideal):<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\"># From the command line in your WordPress directory\nwp transient delete-expired\nwp transient delete --all\n<\/code><\/pre>\n<p>If you do not have shell access, some optimization plugins can handle transient cleanup. As a last resort, you can use SQL, but be careful:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">DELETE FROM wp_options\nWHERE option_name LIKE '\\_transient\\_%' \n   OR option_name LIKE '\\_site_transient\\_%';\n<\/code><\/pre>\n<p>This removes <strong>all<\/strong> transients (not just expired ones). Most plugins will recreate them, but always have a backup and test the effect on staging first.<\/p>\n<h3><span id=\"2_Reduce_Oversized_Autoloaded_Options\">2. Reduce Oversized Autoloaded Options<\/span><\/h3>\n<p>Sometimes a single option holds a huge serialized array (for example, a page builder cache, analytics data, or API response) and is marked as <code>autoload = 'yes'<\/code>. That means every page load unserializes and parses this big chunk.<\/p>\n<p>After you run the earlier query listing the largest autoloaded options, inspect the suspicious ones:<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">SELECT option_name, autoload\nFROM wp_options\nWHERE option_name = 'suspect_option_name';\n<\/code><\/pre>\n<p>Then check the plugin or theme responsible. Often you can:<\/p>\n<ul>\n<li>Disable a specific feature or cache layer in the plugin\u2019s settings.<\/li>\n<li>Update to a newer version where this behavior is fixed.<\/li>\n<li>Move that data into non-autoloaded options or a custom table (requires development work).<\/li>\n<\/ul>\n<p>If you are sure the option is not needed at all (for example, from a removed plugin), you can delete it. If it is needed but does not have to be autoloaded, you can change the flag:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">UPDATE wp_options\nSET autoload = 'no'\nWHERE option_name = 'suspect_option_name';\n<\/code><\/pre>\n<p>This is often the easiest performance win: the data still exists and can be loaded when needed, but it is no longer injected into every request. Again, do this only after confirming what the option does on staging.<\/p>\n<h3><span id=\"3_Remove_Options_Left_Behind_by_Uninstalled_Plugins\">3. Remove Options Left Behind by Uninstalled Plugins<\/span><\/h3>\n<p>Many plugins remove their tables but leave options behind when you deactivate\/delete them. Over years, this accumulates into thousands of unused rows in <strong>wp_options<\/strong> and other tables.<\/p>\n<p>To identify candidates, look for prefixes from plugins you no longer use. For example, if you used a plugin <code>old_plugin<\/code> that stored options like <code>old_plugin_settings<\/code>, you can list them:<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">SELECT option_name\nFROM wp_options\nWHERE option_name LIKE 'old_plugin%';\n<\/code><\/pre>\n<p>Document what you find, verify that the plugin is no longer active, then either:<\/p>\n<ul>\n<li>Use a plugin-specific cleanup tool if the developer provides one.<\/li>\n<li>Or delete them via SQL, for example:<\/li>\n<\/ul>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">DELETE FROM wp_options\nWHERE option_name LIKE 'old_plugin%';\n<\/code><\/pre>\n<p>Do this carefully, one plugin family at a time, with backups and staging tests. Some plugin prefixes are shared with other tools, so do not blindly delete everything that \u201clooks old\u201d.<\/p>\n<h3><span id=\"4_Keep_Autoload_Size_Under_Control\">4. Keep Autoload Size Under Control<\/span><\/h3>\n<p>After you clean transients and unneeded options, re-run the total autoload size query:<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">SELECT \n  COUNT(*) AS autoload_count,\n  ROUND(SUM(LENGTH(option_value)) \/ 1024 \/ 1024, 2) AS autoload_mb\nFROM wp_options\nWHERE autoload = 'yes';\n<\/code><\/pre>\n<p>On most sites, reducing autoload from, say, 20 MB to 2 MB will produce a noticeable TTFB improvement, especially on shared hosting or small VPS plans. This also reduces memory pressure on PHP and MySQL, which plays nicely with the PHP and cache tuning we discussed in our server-side WordPress optimization guide.<\/p>\n<h2><span id=\"Dealing_With_Oversized_Tables_wp_postmeta_Revisions_and_Logs\">Dealing With Oversized Tables: wp_postmeta, Revisions and Logs<\/span><\/h2>\n<h3><span id=\"1_wp_postmeta_Bloat\">1. wp_postmeta Bloat<\/span><\/h3>\n<p><strong>wp_postmeta<\/strong> is a common hotspot, especially for WooCommerce stores, page builders and custom fields. Each product, variation, layout element or SEO field can add several rows of meta data. Over time, this can reach millions of rows.<\/p>\n<p>First, check how big it is and how many rows you have:<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">SELECT \n  COUNT(*) AS rows,\n  ROUND((DATA_LENGTH + INDEX_LENGTH) \/ 1024 \/ 1024, 2) AS size_mb\nFROM information_schema.TABLES\nWHERE TABLE_SCHEMA = 'your_database_name'\n  AND TABLE_NAME = 'wp_postmeta';\n<\/code><\/pre>\n<p>If you have millions of rows, look for:<\/p>\n<ul>\n<li><strong>Orphaned postmeta<\/strong>: meta records whose <code>post_id<\/code> no longer exists in <strong>wp_posts<\/strong>.<\/li>\n<li>Old data from deactivated or removed plugins.<\/li>\n<li>Debug or log data mistakenly stored in postmeta.<\/li>\n<\/ul>\n<p>To find orphaned meta rows:<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">SELECT COUNT(*) AS orphan_count\nFROM wp_postmeta m\nLEFT JOIN wp_posts p ON m.post_id = p.ID\nWHERE p.ID IS NULL;\n<\/code><\/pre>\n<p>If the count is large and you already verified your backups, you can delete them:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">DELETE m\nFROM wp_postmeta m\nLEFT JOIN wp_posts p ON m.post_id = p.ID\nWHERE p.ID IS NULL;\n<\/code><\/pre>\n<p>For WooCommerce-heavy sites, also review plugins that log tracking or analytics into postmeta. Sometimes moving such logs to a dedicated table or external service makes more sense. For deeper MySQL\/InnoDB tuning specifically for WooCommerce, we shared a detailed checklist in <a href='https:\/\/www.dchost.com\/blog\/en\/woocommerce-icin-mysql-innodb-tuning-kontrol-listesi-buffer-pool-indeksleme-ve-slow-query-analizi-nasil-akillica-yapilir\/'>our WooCommerce MySQL\/InnoDB tuning article<\/a>.<\/p>\n<h3><span id=\"2_Cleaning_Up_Revisions_Trash_and_Spam\">2. Cleaning Up Revisions, Trash and Spam<\/span><\/h3>\n<p>Post revisions are stored as separate rows in <strong>wp_posts<\/strong>. Comments live in <strong>wp_comments<\/strong>, and their metadata in <strong>wp_commentmeta<\/strong>. If you never empty the trash or spam, these tables can grow for no benefit.<\/p>\n<p>To remove old revisions (on staging first):<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">DELETE FROM wp_posts\nWHERE post_type = 'revision';\n<\/code><\/pre>\n<p>You can also limit future revisions by adding this line to <strong>wp-config.php<\/strong>:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">define( 'WP_POST_REVISIONS', 10 ); \/\/ or a lower number\n<\/code><\/pre>\n<p>To clean up trash and spam comments:<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">DELETE FROM wp_comments\nWHERE comment_approved = 'spam' \n   OR comment_approved = 'trash';\n\nDELETE FROM wp_commentmeta\nWHERE comment_id NOT IN (SELECT comment_ID FROM wp_comments);\n<\/code><\/pre>\n<p>WordPress has built-in options to automatically delete trash after X days; configure these settings so that garbage does not accumulate silently.<\/p>\n<h3><span id=\"3_Log_and_Statistic_Tables\">3. Log and Statistic Tables<\/span><\/h3>\n<p>Certain plugins create their own tables for logs, analytics or debugging (for example <code>wp_pluginname_logs<\/code>, <code>wp_pluginname_stats<\/code>). These can easily reach gigabytes if not rotated.<\/p>\n<p>In phpMyAdmin, look for tables with names ending in <code>_logs<\/code>, <code>_stats<\/code>, <code>_sessions<\/code> or similar. Check their row count and data size. If they belong to a plugin you still use:<\/p>\n<ul>\n<li>Look for built-in retention settings (e.g. \u201ckeep logs for 30 days\u201d).<\/li>\n<li>If not provided, consider manual purging via scheduled cron jobs.<\/li>\n<\/ul>\n<p>Example: keep only the last 90 days of logs based on a <code>created_at<\/code> column:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">DELETE FROM wp_plugin_logs\nWHERE created_at &lt; NOW() - INTERVAL 90 DAY;\n<\/code><\/pre>\n<p>Do not truncate or drop log tables blindly; some plugins expect a certain schema. When in doubt, consult the plugin documentation or developer.<\/p>\n<h3><span id=\"4_OPTIMIZE_and_ANALYZE_After_Cleanup_With_Care\">4. OPTIMIZE and ANALYZE After Cleanup (With Care)<\/span><\/h3>\n<p>After large deletions, tables can have internal fragmentation. Commands such as <code>OPTIMIZE TABLE<\/code> and <code>ANALYZE TABLE<\/code> help reclaim space and update index statistics, which can improve query performance. For example:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">OPTIMIZE TABLE wp_options, wp_postmeta, wp_posts, wp_comments;\nANALYZE TABLE wp_options, wp_postmeta, wp_posts, wp_comments;\n<\/code><\/pre>\n<p>On big sites, these operations can lock tables for a while, so run them during low-traffic windows and always with backups. On some MySQL\/MariaDB versions and storage engines, <code>OPTIMIZE TABLE<\/code> effectively rebuilds the table.<\/p>\n<h2><span id=\"Indexes_Storage_Engine_Choice_and_Other_MySQL-Level_Tweaks\">Indexes, Storage Engine Choice and Other MySQL-Level Tweaks<\/span><\/h2>\n<h3><span id=\"1_Ensure_InnoDB_Is_Used_for_Core_Tables\">1. Ensure InnoDB Is Used for Core Tables<\/span><\/h3>\n<p>Modern WordPress works best with the <strong>InnoDB<\/strong> storage engine: it provides row-level locking, crash recovery, and better concurrent performance than old MyISAM tables. Check your core tables:<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">SELECT TABLE_NAME, ENGINE\nFROM information_schema.TABLES\nWHERE TABLE_SCHEMA = 'your_database_name'\n  AND TABLE_NAME IN ('wp_posts','wp_postmeta','wp_options','wp_comments');\n<\/code><\/pre>\n<p>If any are still MyISAM, consider converting them (on staging first):<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">ALTER TABLE wp_posts ENGINE=InnoDB;\n<\/code><\/pre>\n<p>Repeat for other tables. Always ensure you have current backups and enough disk space for the temporary files needed during conversion.<\/p>\n<h3><span id=\"2_Add_or_Fix_Missing_Indexes\">2. Add or Fix Missing Indexes<\/span><\/h3>\n<p>WordPress core ships with sensible indexes for its queries, but some plugins create tables with poor indexing or even perform heavy queries on core tables using non-indexed columns.<\/p>\n<p>Typical index improvements include:<\/p>\n<ul>\n<li>Composite indexes on custom plugin tables for columns frequently used together in WHERE\/JOIN clauses.<\/li>\n<li>Indexes on date columns for log tables to speed up pruning.<\/li>\n<li>Ensuring standard WordPress indexes like <code>wp_postmeta (post_id, meta_key)<\/code> exist and are not accidentally removed.<\/li>\n<\/ul>\n<p>Index design is a deep topic, but even a few targeted indexes on big custom tables can dramatically reduce query time and CPU usage.<\/p>\n<h3><span id=\"3_Tune_MySQL_for_Your_Hosting_Plan\">3. Tune MySQL for Your Hosting Plan<\/span><\/h3>\n<p>Database cleanup and MySQL tuning reinforce each other. After reducing table sizes and autoload bloat, you often can:<\/p>\n<ul>\n<li>Allocate a more efficient <code>innodb_buffer_pool_size<\/code> so that hot data fits in RAM.<\/li>\n<li>Adjust <code>query_cache<\/code> (if you are on older MySQL) or disable it properly in newer versions.<\/li>\n<li>Optimize connection limits and temporary table sizes based on your actual workload.<\/li>\n<\/ul>\n<p>On VPS and dedicated servers from dchost.com, you have full control over MySQL settings and can combine these database-side optimizations with PHP-FPM and cache tuning. For larger WooCommerce or content-heavy sites, this is often the point where a well-sized VPS or dedicated server clearly outperforms generic shared hosting.<\/p>\n<h2><span id=\"Ongoing_Maintenance_and_Hosting-Side_Strategy\">Ongoing Maintenance and Hosting-Side Strategy<\/span><\/h2>\n<h3><span id=\"1_Automate_Regular_Cleanup\">1. Automate Regular Cleanup<\/span><\/h3>\n<p>Manual cleanups once every few years are better than nothing, but the best results come from small, regular maintenance tasks:<\/p>\n<ul>\n<li>Scheduled transient cleanup (via wp-cron or real cron jobs).<\/li>\n<li>Regular deletion of old logs and statistics beyond your needed retention period.<\/li>\n<li>Limiting revisions and automatic trash deletion periods.<\/li>\n<\/ul>\n<p>Many of these can be scheduled with cron, which we covered practically in our article about <a href='https:\/\/www.dchost.com\/blog\/en\/cpanel-ve-directadminde-otomatik-gorevler-planlama-cron-job-ile-yedek-rapor-ve-bakim-isleri\/'>automating backups, reports and maintenance with cron jobs on cPanel and DirectAdmin<\/a>. Combine those patterns with the SQL statements in this guide for a robust housekeeping routine.<\/p>\n<h3><span id=\"2_Monitor_Growth_Over_Time\">2. Monitor Growth Over Time<\/span><\/h3>\n<p>Instead of discovering a 5 GB database during a crisis, track table sizes periodically. Simple approaches include:<\/p>\n<ul>\n<li>A small script or cron job that queries <code>information_schema.TABLES<\/code> and logs sizes.<\/li>\n<li>Server monitoring (Prometheus, Grafana, etc.) watching MySQL metrics and disk usage.<\/li>\n<li>Regular manual checks for smaller projects.<\/li>\n<\/ul>\n<p>Watch especially for sudden jumps in <strong>wp_options<\/strong>, <strong>wp_postmeta<\/strong> and any log\/stat tables. That usually means a plugin update or new feature changed how data is stored.<\/p>\n<h3><span id=\"3_When_Cleanup_Is_Not_Enough_Scaling_the_Database\">3. When Cleanup Is Not Enough: Scaling the Database<\/span><\/h3>\n<p>A clean, well-indexed database cannot solve every performance problem. If you have a truly high-traffic WooCommerce store or content platform, at some point you need more CPU, RAM, faster storage (NVMe), or even dedicated database and cache servers.<\/p>\n<p>Signs that you are reaching this stage:<\/p>\n<ul>\n<li>Slow queries remain slow even after cleanup and indexing.<\/li>\n<li>MySQL CPU and IO are saturated during peak hours.<\/li>\n<li>You frequently hit connection limits or lock contention under load.<\/li>\n<\/ul>\n<p>On the dchost.com side, this is where we usually recommend:<\/p>\n<ul>\n<li>Moving from shared hosting to a VPS with enough RAM and NVMe storage.<\/li>\n<li>For very busy stores, separating database and application servers.<\/li>\n<li>Adding Redis for object caching, plus full-page caching where safe.<\/li>\n<\/ul>\n<p>We have separate guides on sizing VPS resources for different applications, choosing between VPS and dedicated servers, and planning high-availability database setups; feel free to reach out to our team if you want help mapping this to your specific traffic profile.<\/p>\n<h2><span id=\"Summary_and_Practical_Next_Steps\">Summary and Practical Next Steps<\/span><\/h2>\n<p>WordPress performance problems are often blamed on hosting, themes or PHP versions, but in many real-world sites the real bottleneck is a bloated database. An oversized <strong>wp_options<\/strong> table with heavy autoloaded options, millions of rows in <strong>wp_postmeta<\/strong> and forgotten log tables quietly punish every page request. The upside is that these issues are measurable and fixable with a systematic approach.<\/p>\n<p>Your practical action plan looks like this:<\/p>\n<ul>\n<li>Ensure you have reliable backups (and test restores).<\/li>\n<li>Set up a staging environment and run all SQL changes there first.<\/li>\n<li>Measure table sizes and autoload volume; identify the biggest offenders.<\/li>\n<li>Clean transients, unnecessary plugin options and orphaned meta\/log data.<\/li>\n<li>OPTIMIZE\/ANALYZE tables where appropriate and make sure you are using InnoDB.<\/li>\n<li>Automate regular housekeeping and monitor growth over time.<\/li>\n<\/ul>\n<p>Once the database is lean, every other optimization you do on the hosting side \u2013 PHP-FPM tuning, Redis, full-page caching, CDN \u2013 delivers better results. If you host your site with dchost.com or plan to migrate, our team can help you choose the right shared plan, VPS, dedicated server or colocation setup for your WordPress workload and combine it with the database practices outlined here. Start with a clean, well-structured database, and you will immediately feel the difference in admin responsiveness, TTFB and overall user experience.<\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"<p>Many slow WordPress sites do not have a PHP or caching problem at all \u2013 the real bottleneck sits quietly inside MySQL. A few megabytes of forgotten options, transients that never expire, or a single oversized table can be enough to push your Time To First Byte (TTFB) up, increase CPU\/IO load on your hosting, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":3386,"comment_status":"","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[],"class_list":["post-3385","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-teknoloji"],"_links":{"self":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts\/3385","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/comments?post=3385"}],"version-history":[{"count":0,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts\/3385\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/media\/3386"}],"wp:attachment":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/media?parent=3385"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/categories?post=3385"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/tags?post=3385"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}