How to Optimize WordPress Database Through Code and Plugin

All the valuable data of a WordPress site, including pages, comments, blog posts, form entries, portfolio items, media, and more is stored on the WordPress database. Additionally, even the configurations and settings of the themes, plugins and website are stored on it.

While static HTML sites store data inside the pages, the WordPress relies on MySQL database to store all the data. When the website is used regularly, the database keeps growing to accommodate the changes. With time, it can get fat and bloated if it isn’t optimized.

When the database gets bloated, it slows down your website performance because the server will require more time to find information from large database. Hence, it is very important to optimize WordPress database to speed up the website.

If database optimization sounds a daunting task to you, then stick with us. The steps below will help you easily optimize the WordPress database through code and plugins.

Note: Before optimizing the database, consider backing it up as the data stored in database is very critical. Any error during the database optimization process can hurt your entire website. Hence, make sure to take a full backup of your WordPress website.

Optimize WordPress database through phpMyAdmin

The phpMyAdmin is a very common way for managing WordPress database. If your WordPress hosting plan comes with control panel (cPanel), then you can use phpMyAdmin database management tool.

At the time of WordPress installation, generally 12 tables are created to store valuable information of your site. As you install more plugins on your site, the number of tables increases. Following is a list of these tables.

1. wp_commentmeta – For storing the meta information about comments.
2. wp_comments – For storing the comments made on the website.
3. wp_links – For storing blogroll links.
4. wp_options – For storing the options defined in the admin settings area.
5. wp_postmeta – For storing post meta information.
6. wp_posts – For storing data about and for posts, pages, and other custom post types.
7. wp_terms – For storing post tags and categories for posts and links.
8. wp_term_relationships – For storing the association between posts, categories and tags and the association between links and link categories
9. wp_term_taxonomy – For storing the description about the taxonomy (category, link, or tag) used in the wp_terms table
10. wp_usermeta – For storing meta information about users
11. wp_users – For storing user information

In the phpMyAdmin, you get an option to check all the tables on your database. You can select the tables you want to optimize, and go on with the process.

In the database, you will find two columns at the end— size and overhead. The size of the table is large if the data stored in it is more. On the other hand, the overhead is a temporary disk space that your database uses to store queries. With regular updates on site, the overhead of the table increases.

When you optimize the database, the overhead will be removed which will reduce the overall size of your database.

How to optimize tables in WordPress database?
To optimize the tables affected by too much clutter (overhead), you need to use the SQL command— OPTIMIZE TABLE. For instance, if you want to optimize the wp_commentmeta table, then execute the following SQL query:

OPTIMIZE TABLE ‘wp_commentmeta’

Further, the phpMyAdmin also allows you to optimize tables from the main drop-down menu. Select the tables you want to optimize, or you can select the Check All option. Then select the Optimize table option and click Go.
You will see a confirmation from phpMyAdmin when the WordPress database gets successfully optimized.

phpMyAdmin also gives you an option to Repair table, which helps you in fixing the corrupted tables. You should consider using this.

Use plugins to optimize WordPress database

1. WP-Optimize

   
   WP-Optimize is one of the most effective plugins to automatically clean your WordPress database so that your site can run at maximum efficiency. It removes all the unnecessary data like trashed/unapproved/spam comments, pingbacks, trackbacks, and expired transient options.

   It also cleans the unnecessary post revisions that unnecessarily consumes megabytes of data. Further, WP-Optimize frees up more space and accelerate website by cleaning up comments table, removing all the spam. It can de-fragment the MySQL tables with a button-press.

   WP-Optimize analyzes the database and reports accurately about database tables that have overhead and wasted space. It helps you in finding exactly what to clean and what not.

   Powered by automation, this tool can run automatic clean-ups which you can schedule on a weekly-basis, or as per the specified period for your blog or business website.

2. WP-DBManager

   
   WP-DBManager is another good plugin like WP-Optimize to manage your WordPress database in case you are not a tech whiz. It comes with a ton of features and has been mainly designed for database optimizations.

   It allows you to efficiently manage and optimize database right from the admin dashboard. All you need to do is to install this plugin and activate it.

   After activation, use the Database menu which include several options like Database backup, Optimize database, Repair database, Run SQL Query, Empty/Drop tables, and more.

   You can use these options to clean up database and other optimization purposes. Further, you can schedule database backups, optimizations and repairs. This plugin helps you run a safe and smooth WordPress site.

3. Anti Spam Plugin

   
   When your website receives a lot of spam comments, they unnecessarily consume space in database. The spam comments take up a lot of rows in wp_comments table.

   To address this, you can install an anti-spam plugin to stop all the spammy comments.

   Further, you can also delete spam comments using this SQL command:

   DELETE FROM wp_comments WHERE comment_approved = ‘spam’

   When any blog post, image, page, or comment is deleted from WordPress, it goes into the trash folder. The trash feature is much like Recycle Bin, which allows you to restore items if you accidently delete them.

   When there are thousands of items in trash, it can bloat the database. The trashed items are by default deleted after 30 days, but you can reduce the number of days using following command.

   define( ‘EMPTY_TRASH_DAYS’, 5 ); // 5 days

Conclusion:

It’s not that hard to optimize WordPress database if you use proper plugins and coding that automate certain processes. If you have any query, let us know in the comments section below.