Have you ever wondered why a WordPress website performance decrease with time? A lot depends on the WordPress databases, which is the storehouse of all website content. In this article, we will understand how the database works and the steps you can take to optimize the database.
Understanding WordPress Databases
Before you start optimizing your website database, let us understand the fundamentals of WordPress database tables.
Below is the screenshot of a tabular WordPress site structure. WordPress only supports MySQL 5.0.15 or greater or other versions from MariaDB for its database. The diagram explains the database and relationship between its tables which originated during WordPress installation.
The fundamental WordPress database tables shown in the diagram helps to store post tags and categories, blogroll links, description about taxonomy, users, post meta information and much more.
Importance of Optimizing WordPress Database
As mentioned previously, WordPress works with MySQL database, which is further supported by PHP programming language. PHP is used to analyze and render the information saved in the MySQL database. MySQL is the source technical infrastructure behind every WordPress installation, containing all the necessary data bytes about the WordPress site.
A basic WordPress installation has eleven tables. However, when you create or install new themes or plugins, the database creates new tables to save the information. While changing or removing plugins and themes, a lot of information is left behind in the database. Whenever you revise a post, WordPress database additionally creates a draft version, and as the inessential information pile up, the database becomes huge, and without proper database maintenance, the website speed decreases.
As we know, a slow site is not good for an online business. By optimizing and clearing your WordPress database, you can lessen the site loading time and create extra space for user data storage.
Creating Backup and Optimizing WordPress Database
Before you start optimizing your WordPress database, you need to create a database backup because messing up with a database can completely break down your website. Some plugins are listed below to create a database backup.
BackupBuddy is a comprehensive solution for all your site backup needs. With the help of BackupBuddy, you can create a backup of your database on email, FTP, Dropbox, Amazon Web Services, Rackspace and hard drive. BackupBuddy is a premium plugin, so you will have to pay $80 for at least two websites.
Updraft Plus is a free plugin that supports backup storage to email, Rackspace Cloud, FTP, Amazon Web Services, Dropbox, Google Drive, OpenStack Swift, DreamObjects and hard drive. With the paid version, you can save the backup to Microsoft OneDrive, Google Cloud Storage, Microsoft Azure, SCP, WebDAV, and SFTP.
BackUpWordPress is a very popular backup plugin that allows the user to automatically backup the WordPress database. You can also schedule the backup process for your database and files separately. This plugin is highly user compatible and easy to set-up.
If you do not want to save the backup on a hard drive or email, you need to purchase an additional extension. The extension will allow you to store the database and other files on Google Drive, Dropbox, and other cloud storage services.
Choose any of the plugins mentioned above to create a backup for the WordPress site. After saving the backup, install and activate any of the database optimization plugin listed below.
WP-Optimize is the top-rated plugin for optimizing and cleaning your WordPress site. Schedule weekly optimization tasks and the plugin handles the rest. It will automatically clean up spam comments, post revisions, and other unproductive information. With WP-Optimize, you can set a schedule weekly, bi-weekly or monthly optimisation pattern. You can automatically trigger a pre-optimized backup via the UpdraftPlus plugin.
Optimize Database and Cleanup plugin, also known as WP Clean Up Optimizer is useful for optimizing, repairing and cleaning your database. All the unwanted information like pingbacks, comments, revision drafts and meta information is scrubbed away from the database. If you go for the paid version, you will get additional features and premium customer support. WP Clean Up Optimizer plugin also offers specific table optimization and automatic scheduled optimisation functions.
With a rating of 4.5 out of 5 stars, WP DBManager is one of the most popular database optimisers. As the name suggests, this plugin can completely manage your WordPress site database. You can quickly clean, restore, backup, repair and optimize your site with this single plugin.
Additionally, you can schedule your website for automatic backup, repair, and optimization. Just install and activate the plugin, and it will take care of all your site optimization needs.
Often, a WordPress site slows down due to the presence of multiple plugins which overloads the system and impacts the database. The P3 plugin gives an analysis of the installed and deactivated plugins, highlighting the space it’s taking in the database, and how much space can be saved by removing specific plugins.
The plugin is recommended for sites with a lot of content. Every blog post or page creates multiple versions/drafts in the database. The plugin removes redundant post/page revision data, deletes comments, deletes trashed posts/pages, deletes unused tags, pingbacks, trackbacks, expired transients, and orphan post meta items. A dedicated log file of all the actions is available.
Manual Database Optimization
Can you optimize your database without a plugin?
Although plugins do make the optimisation work much easier, you can also optimise your site database manually. Follow the steps mentioned below to clean your database without plugin:
- Login to your hosting account and navigate to cPanel.
- Under the list of tools, you will see “PHPMyAdmin”. This section allows you to edit and preview your site MySQL database manually.
- Go to the “Table” column. You will see all the tables related to your database. On this screen, you will be able to check the space each table is occupying.
If you are not good with coding, you may find PHPMyAdmin confusing. Before deleting any table, go through each table and identify the information you want to remove. Remember, a small mistake can remove your site’s valuable information. Be careful with the “Drop” link, as it will permanently eliminate a table and the data saved inside the table.
Once you are sure about the data you want to delete, enter the required MySQL commands to remove it.
If you are not accustomed to MySQL, opt for automatic site optimisation through the above-mentioned plugins.
Before using any database management plugin, make sure to take a full site backup. If anything goes wrong, you can retrieve the backup and get the site back easily.