Announcing Duplicator WP-CLI Commands (And How They Speed Up Backups)
Looking for a smarter way to manage site backups? Explore Duplicator's new features, including customizable WP-CLI commands and permission settings!
Announcing Duplicator WP-CLI Commands (And How They Speed Up Backups)
Joella Dunn
Joella Dunn
John Turner
John Turner
Do you need to optimize your WordPress database?
Behind the scenes, unnecessary data could be cluttering your WordPress database. This can make your website load slower, leading to a higher bounce rate and lower conversion rates. Fortunately, it’s a fixable issue.
In this tutorial, we’ll show you how to optimize your WordPress database!
Your WordPress database is where all the essential data lives — including blog posts, web page content, usernames, and passwords.
When you first create a website, WordPress establishes a MySQL database on your web server. Every time someone visits your website, their browser sends requests to your server to retrieve information from your database.
Your WordPress database uses a technology called MySQL, which is an open-source database management system. It separates your data into different tables that work together to create your site.
WordPress has 12 core tables. Here’s what each one stores:
These are the foundational tables in every WordPress database. However, as you install plugins and themes, these could add extra tables.
With time, your database collects post revisions, spam comments, and other unnecessart data. The heavier your database, the more your site will slow down.
When your load times are high, online visitors will quickly leave your website. If you want more traffic, better user experience, and improved search engine rankings, it’s time to optimize your database.
Here are some more reasons to optimize your database:
Database optimization means improving the efficiency of your WordPress database, which can speed up your website and enhance user experience. On the other hand, database repair refers to fixing any issues or errors that may be affecting its performance.
Imagine your database as a warehouse, filled with information. As data is added and removed, some parts of the warehouse are cluttered while others are empty.
That’s where database optimization comes into play. It organizes your data, just like you would organize a warehouse, making it easier to access the necessities.
Now, imagine the warehouse’s structure is compromised – maybe a wall is crumbling or the roof is leaky. That’s a clear-cut case for a repair job. Similarly, if your site is experiencing recurring errors due to malfunctions in your database, it’s time for a database repair.
So, while both optimization and repair are essential maintenance tasks, optimization ensures a smooth backend operation. However, repairs address specific performance issues caused by errors in your database.
Before you start optimizing your database, it’s important to back it up. This way, you can easily restore your database if anything accidentally gets deleted.
Duplicator is the best plugin for backing up your WordPress database. You can use either the free or premium version, although convenient features like automatic cloud backups are only available if you upgrade.
Once you install Duplicator, create a new backup. Name it using dynamic tags and choose a storage location.
In the Backup section, select Database Only. However, you can also back up your entire WordPress site or create a custom backup file.
Finish building the backup. Now, you’re free to continue optimizing your database!
Keep in mind you can restore your database at any time. If the backup is stored locally, go to your Duplicator backup log and hit the Restore button.
If you think a database error could lock you out of your WordPress dashboard, be sure to set a disaster recovery point. Click on the recovery ion next to a full backup.
Then, copy the recovery link.
If anything happens, paste this link into a browser window. This will instantly roll back your site to a previous version.
At this point, you’re ready to optimize your database! Let’s get started.
It can take time and technical experience to manually optimize your database. To automate this process, you can install a plugin like WP-Optimize.
WP-Optimize is a multi-functional tool that can fully clean up your website. It can enable caching, compress images, minify code, and optimize the database. Plus, it’s a completely free plugin.
To get started, install and activate WP-Optimize. Then, go to WP-Optimize » Database.
In the plugin settings, you’ll see a list of actions you can run on your database. These include cleaning up your post revisions, unapproved comments, and more.
Check all of the optimizations you want to run. After this, click on Run all selected optimizations.
You can also run optimizations one at a time. To the right, click on Run optimization.
At the bottom of the page, you’ll see some actions with a warning next to them. These are more intensive operations. If your server crashes while these are running, your data could become corrupted.
After this, your database will automatically be optimized. There’s no need to continue to the next steps since WP-Optimize did them for you!
Another way to optimize your database is with phpMyAdmin, your database manager. This can give you more control over the optimization process.
First, sign into your web hosting provider’s control panel. Usually, you can access phpMyAdmin in your cPanel dashboard.
In phpMyAdmin, click on the Databases tab. Then, select your database name.
Now, you should see a list of database tables. Scroll to the bottom and hit Check All. Expand the With selected dropdown menu and choose Optimize table.
PhpMyAdmin will automatically create an SQL query that optimizes your database tables.
Using database queries, the performance of your website will improve. The size of your database will decrease, speeding up your page load times.
WordPress has a built-in way to optimize your database. All you’ll need to do is add this SQL command to your wp-config.php file.
define( 'WP_ALLOW_REPAIR', true );
Next, use this link to open the WordPress optimization tool:
http://www.yourwebsite.com/wp-admin/maint/repair.php
On this page, you can allow WordPress to repair and optimize your database.
Once this is done, remove the added code from your wp-config.php file.
When you delete items like old posts, they’ll get moved to the Trash folder instead of permanently deleted. As a result, your database could become bloated over time.
WordPress permanently deletes files after 30 days. But, you might want to empty your trash folder more often to optimize your database.
In your wp-config.php file, add this code:
define( 'EMPTY_TRASH_DAYS', 7 );
This changes the retention period to 7 days. However, you can change this number to any time that suits your needs.
If you run a blog, you’re probably constantly revising old posts to improve your SEO ranking. You might not realize that these post revisions are taking up space in your database.
To fix this problem, you can reduce the number of revisions allowed in your database. Go to your wp-config.php file and add this line:
define( 'WP_POST_REVISIONS', 2 );
This will only keep 2 post revisions for each post on your site.
If you want to, you can disable revisions altogether with this code:
define( 'WP_POST_REVISIONS', false );
Keep in mind that this prevents you from accessing any old post revisions. So, consider if you’ll ever need these in the future.
If you don’t block spam, your WordPress website could be flooded with spam comments. Like other files, spam comments are deleted after 30 days. During this time, they could add hundreds of unnecessary rows to your wp_comments database table.
A good way to stop receiving spam comments is to install a spam-blocker plugin. Akismet is a popular option that has over 5 million active installations.
After you install Akismet, it will automatically protect your site from spam comments. These will be sent to your spam queue.
You can also have Akismet delete spam so that you never see it.
Otherwise, any spam comments will be deleted after 15 days. If you want to clear out your spam comments more frequently, click on Empty Spam.
This reduces bloat in your database.
Tags are a great tool for organizing your website’s content. They help group topics on your site, navigating users to relevant articles.
Over time, you may stop using certain tags. You might not even realize how much room they’re taking up on your database.
To delete them, use phpMyAdmin to connect to your database. Then, run this SQL query:
SELECT * FROM wp_terms wterms INNER JOIN wp_term_taxonomy wttax ON wterms.term_id = wttax.term_id WHERE wttax.taxonomy = 'post_tag' AND wttax.count =0;
If you have a different prefix than wp_, make sure to update this command.
Now, run these commands to delete all of your unused tags at once:
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);When you publish a post that links to an external website, you’ll send them a server notification. This is called a pingback. Trackbacks are similar, except they are sent manually.
If you run a popular website, you might have thousands of pingbacks and trackbacks in your database. So many sites linking to your content could take up unnecessary database space.
Fortunately, you can disable pingbacks and trackbacks. In your dashboard, find Settings » Discussion. Uncheck the first two options in the Default post settings.
If you already have a lot of trackbacks and pingbacks on your database, you can delete them in bulk. Simply run this command:
UPDATE wp_posts SET ping_status= “closed”;
Now your database is a little more optimized!
Transients are WordPress functions that allow developers to keep important information in a database for a specific amount of time. These records are kept in the options table.
Once transients expire, they can clutter your database. In turn, transients can negatively affect your site’s performance.
To delete transients, you can install the Transients Manager plugin. This gives you a beginner-friendly interface to start editing the transients on your site.
Go to Tools » Transients. From your list of transients, you’ll see if any are expired.
Bulk select your transients. In the Bulk actions menu, choose Delete Expired.
Now, you won’t have any expired transients bloating your database!
Certain WordPress plugins store a lot of data in the database. Every new plugin you install adds to your database, but some are heavier than others.
Here are some plugins that can add bloat to your database:
If you notice that a specific plugin is using a lot of database storage, consider removing it. If you’re not sure if you should reduce your plugin use, read our guide on How Many WordPress Plugins Are Too Many.
You can optimize databases in WordPress by emptying the trash. It’s also a good idea to delete post revisions, spam comments, unused tags, expired transients, pingbacks, trackbacks, and resource-heavy plugins. You can also use a database cleanup plugin to remove unnecessary data from your database.
The best free WordPress database optimization plugin is WP-Optimize. It’s not only a caching plugin, but a full database cleaner. However, other popular options include Advanced Database Cleaner and WP-Sweep. These are all available for free on WordPress.org.
To reduce the size of your WordPress database, you can install a plugin like WP-Optimize. Alternatively, log into phpMyAdmin and optimize all of the tables in your database. You can also try manually deleting post revisions, trashed comments, and other unnecessary data.
At this point, you have everything you need to optimize your WordPress database!
While you’re here, you may like these extra WordPress tutorials:
Do you need to save a copy of your database before you start optimizing it? Download Duplicator Pro to instantly back up your database and restore it in one click!
Disclosure: Our content is reader-supported. This means if you click on some of our links, then we may earn a commission. We only recommend products that we believe will add value to our readers.
