BEST PRACTICES TO OPTIMIZE YOUR WORDPRESS DATABASE

Your WordPress site can be slow for many different reasons; a bloated database running in the background is one of them. A professional WordPress maintenance workflow includes regular database optimization during which you get rid of unnecessary data, and locate and fix errors and inconsistencies in your database.

WordPress database optimization doesn’t require any specific programming knowledge, although you occasionally need to edit the wp-config file. In this article, we will show you a couple of best practices about how to optimize your WordPress database.

START WITH A BACKUP

Before getting started with optimization, always create a backup of your database. In this way, if anything goes wrong, you won’t lose any data and can return to a previous version of your site. There are different solutions to back up a WordPress site, here are the most common ones:

• you can create a full site backup from the cPanel of your hosting account,
• you can export all your content including your posts, pages, comments, and other post types using the Tools > Export menu in your WordPress admin area,
• you can use a backup plugin such as BackupBuddy or VaultPress that allows you to create different kinds of custom backups.

DELETE THE CONTENT YOU DON’T NEED

One of the most important things of database optimization is to get rid of the overhead content on your site. As each post, page, comment, and post revision is an entry in your database, you can free up a lot of space by deleting unused, obsolete, or duplicate content right from your WordPress admin area.

If you have a WordPress site for a while, you can find such content in many different places. First and foremost, it’s worth deleting the Trash folder of your Posts and Pages (even if WordPress automatically deletes trashed posts after 30 days). You can also go through your Drafts and Pending posts to see if you really need all of them.

You can also check if you have any taxonomies you don’t use. WordPress has two kinds of taxonomies by default: tags and categories; you can find both of them in the Posts menu. Database optimization is also a good opportunity to think through your taxonomy structure, get rid of duplicate tags and categories, and improve the findability of your posts.

Besides default posts and pages, you can also have custom post types such as calendars, recipes, or affiliate links that might include content you don’t need anymore.

REMOVE THEMES AND PLUGINS YOU DON’T USE

Although WordPress saves themes and plugins into the wp-content folder instead of the database, it’s still a good idea to delete the ones you don’t use. Many plugins and some of the more complicated themes create extra tables inside your database. Besides, all plugins and themes store their configuration options in the wp-options table.

Plugins and themes developed following WordPress’ coding standards clean up after themselves when you remove them from your site. In this case, you only need to delete them from the WordPress admin area, from the Appearance and Plugins menus. Note, however, that it’s not enough to only deactivate them, you also need to hit the Delete button. If your plugin doesn’t clean up after itself, you can still get rid of the related database overhead using one of the methods below.

OPTIMIZE YOUR TABLES IN PHPMYADMIN

You can run a database optimization query right from your phpMyAdmin interface. PhpMyAdmin is the application that allows you to access your raw database. You can reach phpMyAdmin from the cPanel of your hosting account, from the Databases menu.

In phpMyAdmin, open the database belonging to your WordPress site and check the tables you want to optimize. You can also choose the Check All option, as you can see on the image below:

Here, scroll down the dropdown list you can find below the tables. From the Table maintenance group, select the Optimize table option and run the query.

The Optimize table option runs the OPTIMIZE TABLE command on your database, which, according to the MySQL documentation:

“reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table.”

Besides OPTIMIZE TABLE, you can also run the REPAIR TABLE query from the same phpMyAdmin menu, which “repairs a possibly corrupted table”.

RUN WORDPRESS’ DATABASE OPTIMIZATION TOOL

WordPress Core also has a built-in database optimization tool. It’s turned off by default, however, you can activate it by editing your wp-config.php file. You can find wp-config in the root directory of your WordPress install on your server. Open it in your code editor and add the following option to the top of the file:

define( 'WP_ALLOW_REPAIR', true );

You can find the optimization script under the http://your-site.com/wp-admin/maint/repair.php URL, where you need to replace your-site.com with your own domain.

Here, you can choose from two options:

1. Repair Database – it looks for and repairs common database problems,
2. Repair and Optimize Database – besides all the tasks performed by Repair Database, it also attempts to optimize your database to improve performance.

In most cases, the first option is enough, but if you want an in-depth cleanup, choose the second one. Wait until the tool repairs and/or optimizes your database, then remove the option you have added to your wp-config file (otherwise, anyone on the web can see sensitive information about your database).

USE A DATABASE CLEANUP PLUGIN

If you don’t want (or can’t) access your raw database and your wp-config file, or you want to perform advanced database optimization tasks, you can also put a plugin into use. Below, we have collected the three best database cleanup plugins for you.

Note that you shouldn’t use more than one database optimization plugins at the same time, as these plugins have similar functionalities that can interact with each other in unexpected ways.

1. WP-OPTIMIZE

WP-Optimize is a free plugin that takes care of all kinds of optimizations on your WordPress site. Besides cleaning your database, it also compresses your images and caches the content on your site. Its database cleanup feature:

• removes all unnecessary data, even expired transients hidden in the wp-options table,
• optimizes all your MySQL tables,
• lets you run regular automatic cleanups,
• shows database statistics,
• and more.

The plugin is frequently updated and the support team is responsive, so it’s a good and safe solution if you want to run automated cleanups on a regular basis.

2. ADVANCED DATABASE CLEANER

Advanced Database Cleaner is another free database optimization plugin that comes with many advanced options. It allows you to remove all kinds of unused, obsolete, and duplicate content types, such as:

• old revisions, drafts, and auto-drafts of posts, pages, and custom post types,
• pending, spam, and trash comments,
• orphan metadata and relationships,
• expired transients,
• and more.

You can also schedule and run automatic cleanups, view and clean tables, and display and view items before removing them. Besides, Advanced Database Cleaner supports multisite installs, too.

3. OPTIMIZE DATABASE AFTER DELETING REVISIONS

Optimize Database after Deleting Revisions is a database optimization plugin that lets you clean up your database with one single click. If you need a simple solution that doesn’t require much configuration, Optimize Database after Deleting Revisions can be the best choice for you. With this plugin, you can remove:

• revisions of posts, pages, and custom post types,
• trashed posts, pages, and comments (also spam comments),
• orphan postmeta items,
• unused tags,
• expired or all transients,
• pingback and trackbacks,
• and more.

It also supports WordPress Multisite and adds an “Optimize DB” link to your top admin bar with which you can run a one-click database optimization any time you want.

Source: https://www.developerdrive.com/optimize-wordpress-database/