Reduce the Size of Your WordPress SQL Database

Reduce the Size of Your WordPress SQL Database

If you run your WordPress website solely through the dashboard interface, you may not spend much time exploring the back end services that run on the server. Of these, the SQL database is among the most important. This database holds much of your website’s data, such as the text of your posts and comments. Each time a viewer loads a post on your website, WordPress searches the SQL database on your server and renders the data as a webpage. The larger the database is, the more time it may take to render pages for your website’s visitors. People who use broadband Internet connections expect quick response times as they browse the Web. If a website responds slowly, they may never come back. Using these steps on my websites has resulted in SQL database size reductions of up to 75-percent and significantly faster response times.

Curious about the size of your WordPress SQL database? Install the Database Size WordPress plugin before performing these steps. If you have multiple websites running on the same hosting account, this plugin can also help you determine which of your SQL databases is the one for the website that you are currently working with; click “MySQL Databases” on your host’s control panel to see a list of your database names. Find the database that corresponds with the size shown at the top of the page after activating the Database Size plugin.

Note: HostGator is my Web hosting provider. If your provider uses cPanel and phpMyAdmin, you should be able to follow these instructions exactly.

  1. Disable WordPress post revisions and delete the old revisions stored in the database.
  2. Go to the Comments section of the WordPress dashboard, and click the “Spam” link at the top of the page.
  3. Click the “Empty Spam” button to delete all of the comments caught by the spam filter.
  4. Log in to the control panel for your Web hosting provider, and click “phpMyAdmin.”
  5. Click the name of your SQL database on the left side of the window. When you look at the main area of the window, you should see some fairly significant numbers in the “Overhead” column of the “wp_commentmeta,” “wp_comments” and “wp_posts” rows. This is all of the empty space that you have created in your database by following the previous steps — probably several megabytes. Time to remove that overhead from the SQL database.
  6. Select the “wp_commentmeta,” “wp_comments” and “wp_posts” check boxes.
  7. Click the “With Selected” drop-down menu at the bottom of the list, and select “Repair Table.” This rebuilds the selected sections of the SQL database, eliminating all of the overhead space. Your SQL database is now significantly smaller, and the performance of your WordPress website should be noticeably improved.

Comments are closed.