Database optimization is crucial for every website. In this article you will learn how the WordPress database works, why you need to optimize it and how you can do that, as well as explore the best optimization plugins along with some must know tips.
Table of Contents
WordPress is a popular content management system (CMS) used by millions of websites worldwide. As a CMS, WordPress relies on a database to store and manage the data for your website, including posts, pages, comments, and user information. Over time, however, this database can become cluttered with unnecessary data and can start to slow down your website.
Database optimization is the process of cleaning up and organizing the WordPress database to improve its performance and efficiency. To keep your WordPress website running smoothly, it’s important to optimize your database regularly. This can involve cleaning up unnecessary data, such as trashed posts and comments, as well as optimizing the database tables to improve performance.
There are several ways to optimize your WordPress database, including manual cleanup and the use of plugins. In this article, we’ll provide an overview of some tips and techniques for optimizing your WordPress database and a review of some popular plugins that can help you automate the process. Whether you’re a WordPress beginner or an experienced user, these tips and tools can help keep your website running smoothly and efficiently.
By following the tips and resources shared here, you’ll be able to keep your WordPress database optimized and ready to deliver the best experience to your site users.
How WordPress database works
Any data-driven website (also referred to as a dynamic website) is only as good as its database. As aforementioned, your WordPress database contains essential data that makes up your website. This includes posts, pages, comments, users, themes, plugin settings, and any custom data added by plugins or themes.
To help you understand the importance of WordPress database optimization, how it works, and make the most of it, we should start with some basics on WordPress databases. Despite their intuitive interface and ease of design, WordPress sites use MySQL or MariaDB relational databases to persist complex data objects that keep the sites functioning optimally.
Part of creating a new WordPress website involves providing a name of the database as well as the credentials of the database user and host. Once the above information has been provided WordPress then creates the tables in that database automatically.
Usually, your database, alongside your site, is stored on a server owned by a hosting service provider commonly referred to as a database host.
By default, 12 core database tables are created during any standard WordPress installation. Refer to the image below.
The WordPress database is based on the MySQL database management system (MariaDB is also derived from MySQL), a widely-used, open-source database platform. MySQL stores data in tables, which are organized into rows and columns. Each table in the WordPress database has a specific purpose, such as storing posts, pages, or users. You can also access this database and send commands using a Structured Query Language (SQL).
Typically, when a user visits a WordPress site, the site’s PHP code retrieves the necessary data from the database and displays it on the page. This process is called database querying. Under the hood WordPress uses a specific PHP object of a class called wpdb that provides an API to interact with the database, to retrieve and write the necessary data instead of writing raw SQL queries directly. Different plugins and themes as well as the WordPress core itself use the WordPress database: they add and modify records in the database. Over time, these records can go out of control and turn the database content into a cluttered mess.
Why do I need to optimize my WordPress database?
Regardless of your business niche and the site’s simplicity, just about everything that runs on your site is data that is stored on a database. For this reason alone, you need to pay close attention to the state of your database if you want to keep your site running optimally.
More specifically, optimizing your database can be beneficial in the following ways:
Improve site performance
Improving your site’s performance is one of the primary reasons you need to optimize your WordPress database. In today’s fast-paced business environment, users are not likely to wait more than 2 seconds for your site to load. The average user is likely to abandon your site and deem it broken if it takes more than 4 seconds to load. Site speed is also key in SEO and one of the few signals that Google uses when ranking sites on mobile and desktop.
As time passes, your database will likely pile up useless data such as spam content, post revisions, and orphaned metadata. Optimizing your WordPress database includes removing unused database records ( tables and rows), which considerably improves the efficiency of your database and its ability to serve pages quickly.
Improve user experience
In a world with so many digital services and web pages offering similar services, providing the best user experience is one of the key ways for your business to stand out. The same case applies to WordPress sites where there is a high likelihood that there exist a couple of similar sites offering services identical to yours.
It is not enough to have an aesthetically appealing WordPress site; it needs to be responsive and functional. Optimizing your WordPress database can lead to improved load speeds and interactivity, which are essential for a good user experience.
Free up storage resources
Disk space is a digital asset that represents the storage space occupied by your WordPress site and related files such as plugins, themes, videos, and code. Usually, this space is provided by your hosting provider, and they can impose restrictions on how much of the space you can use. Data in the database also often is counted toward that storage. Database size of a single site can reach from a few MBs to tens of GBs or even more.
Leaving unnecessary records on your WordPress database is likely to take up disk space, and as your site grows, this is likely to limit your ability to upload useful content. WordPress Database optimization allows you to get rid of records such as old spam comments, unread emails, drafts, and unneeded settings or tables and rows created by plugins and themes, thus redeeming essential storage space.
Improve your site’s security
Scheduling regular database optimizations for your WordPress site allows you to eradicate outdated database records, spam comments. Such records at least can lead to spam. But they may also contain much more dangerous things, such as SQL injections that can lead to bot attacks, site crashes, data leaks and money losses.
Therefore regular WordPress database optimizations can keep your WordPress site secure.
How to optimize your WordPress Database?
One important practice in WordPress database optimization is to be able to monitor and manage the size of your WordPress database. You can use the methods below to determine the size of your WordPress database and compare it with the default size.
- Use the WordPress plugin: Several WordPress plugins can help you determine the size of your database. One such plugin is WP-DB Manager. This plugin allows you to optimize, repair, and back up your database, providing information about the database’s size.
- Use the MySQL command line: You can use the MySQL command line to determine the size of your WordPress database. First, log in to your MySQL server or phpMyAdmin and select the database you want to check. Then, use the following command to determine the size of the database.
SELECT table_schema "Database Name", sum( data_length + index_length ) / 1024 / 1024 "Database Size in MB" FROM information_schema.TABLES GROUP BY table_schema;
This command will show you the size of all databases on your MySQL server, including the WordPress database.
Alternatively, you can use phpMyAdmin and see the size of each table (number of rows and size in KBs) in the database. Select the list of tables, and in the main panel, you will see the data per table. See the screenshots below.
It is difficult to provide a default size for a WordPress database, as it depends on the number and size of the posts, pages, and other data stored in the database. However, a fresh installation of WordPress with no content will typically have a database size of around 10-15 MB.
Suppose you want to compare the size of your WordPress database to the default size. In that case, you can use the method described above to determine the size of your database and then compare it to the approximate size of a new WordPress installation.
There are several methods and tools that you can leverage to optimize your WordPress database without necessarily learning the intrinsics of managing databases. You can use phpMyAdmin to manually delete unnecessary and outdated database tables that may slow down your website. Alternatively, you can use a WordPress database optimization plugin such as WP-Optimize.
Optimize WordPress Database manually
Manual WordPress database optimization entails using a standard web-based tool known as phpMyAdmin or directly using the MySQL console to interact with the database. In this section, we will explore how to optimize your WordPress MySQL database using phpMyAdmin manually.
phpMyAdmin is free software that gives you the ability to manage your WordPress MySQL database and perform administrative and maintenance operations on tables. Other actions that can be accomplished through phpMyAdmin include periodic backups, editing, importing, exporting, or deleting data inside database tables, and other optimization activities.
Most hosting solutions come with phpMyAdmin preinstalled, which can be easily accessed by clicking a button through control panels such as cPanel or Plesk. While their interfaces may differ, ideally, you should be able to find phpMyAdmin in the database section.
While many of the instructions in this article are unlikely to cause your site any harm, we recommend backing up your site’s data before proceeding. This should come in handy in the event of an unlikely action triggering permanent data loss.
We at 10Web ensure that you’ll always have not just one but 10 restore points to rely on: 10Web platform has an automated real-time backup service, guaranteeing you’ll never experience any data loss and can focus on other aspects of your website. You can get started for free here.
WordPress hosting that's fully automated
Host on 10Web's high performance infrastructure and enjoy all the benefits of a secure Google Cloud Partner hosting.
No credit card required
Once you have backed up your data:
- Proceed to click on the phpMyAdmin button to open the Interface below.
- Make sure you’re on the database tab. The list of databases should be displayed as shown below.
- Select the tables you want to optimize using the checkboxes next to each table. Alternatively, navigate to the bottom of the page and click the “check all” box to select all of them.
- Click on the dropdown next to the “check all” box.
- Select the “Optimize table” option on the dropdown next to the check box.
Once you click on the optimize option, all the selected database tables will be defragmented and optimized to execute queries much faster. Defragmenting a database means reorganizing the data stored in the database so that it is stored more efficiently. This process can improve the performance of the database and can also reduce the amount of space that the database occupies on the server. You will get a confirmation message from phpMyAdmin letting you know that your tables have been optimized.
Note that this method does not allow you to get rid of the unnecessary records in your WordPress database. However, you can achieve this using the cleaner plugins we have discussed in the section below.
The best WordPress database cleaner plugins
Rather than manually selecting a list of databases to optimize, you can significantly cut off your workload by leveraging a plugin tool to optimize your WordPress database automatically. With just a single click, you can install any of the trusted plugins below and keep your website running optimally.
While many plugins are touted to be the best in cleaning and optimizing WordPress databases, we have carefully curated a list of the top plugins based on configuration options, performance, reviews, and ratings.
1. WP-Optimize
With over 1 million active installations, WP-Optimize is perhaps the most popular database optimization plugin for WordPress. Using WP-Optimize, you can perform advanced caching, clean up your database and perform many more actions that keep your WordPress site fast and thoroughly optimized.
Once installed, navigate to the WP-Optimize>Database section, where you can choose different categories of optimization actions that you want to execute. For instance, you can clean up post revisions, old drafts, pingbacks, spam comments, and more from your WordPress database.
WP-Optmize has auto-optimization capabilities, allowing you to schedule daily, weekly, or monthly optimizations and select specific items to execute during this process. The premium version also allows you to select the exact date and time to perform WordPress database auto-optimizations.
Key Features
- Under the tables tab, you can select and optimize individual tables.
- Database and site caching options to retrieve space lost to data fragmentation
- Enhanced logging and reporting for premium users.
- Multi-site support.
WP-Optimize has a free version that gives you essential features such as database cleaning and caching. However, to access the advanced features, you must purchase a premium version starting from $49 for two year-long licenses.
2. Advanced Database Cleaner
The advanced database cleaner plugin enables you to get rid of “garbage” that may be making your site sluggish. Over time your WordPress database may have accumulated unnecessary DB records such as spam comments, old revisions, and orphaned posts. You need to get rid of them if you want to reduce database size and speed up your site. The Advanced database cleaner allows you to optimize your WordPress site by getting rid of clutter and streamlining backups in the process.
Once installed and activated, you will be able to view items that can be deleted under the General clean-up tab on the dashboard below. You view details about the elements by clicking the view button and also be able to select specific rows to be removed or ignore the most recent changes to your database when cleaning up.
Using the scheduler, you can also schedule clean-up and optimization tasks to run at a specific time by choosing from the six provided frequencies. Furthermore, you can choose specific elements to be included in the schedule and automate tasks to run at different times.
Under the tables tab, you can also view all your WordPress database tables. With the paid version, database tables are automatically categorized according to the creator, whether they are associated with a theme or plugin or are orphaned. This is a very useful feature, since it is not always easy to understand which plugin is responsible for a particular table or properly remove orphaned tables by simply skimming through the dashboard.
Key features
- Able to delete old comments, trash posts, spam comments, pingbacks, and orphaned metadata.
- Search and filter items based on their names and values.
- Schedule tasks based on frequencies i.e., daily, weekly, hourly, twice a day, or monthly.
- Notification if any of your database tables need optimization.
- Repair damage or corrupt database, if any.
- Compatible with multisite installations.
- Classify and detect orphaned database tables and perform deep database optimization with the pro version.
The Advanced database cleaner has a freemium version and paid plans starting at $39 for a lifetime license for two sites.
3. Optimize Database after Deleting Revisions
Optimize database after deleting revisions is a free one-click database cleaner and optimizer that can help you quickly get rid of unused WordPress database items such as spam comments and revisions. Besides that, it can also help you perform other optimization activities, such as deleting specific databases.
Once installed, navigate to the Settings >> Optimize Database section and configure your clean-up settings. Note that on this page, you can select the type of posts whose revision you want to get rid of.
Like other tools we have highlighted above, you can also schedule automatic cleanups on an hourly, daily, weekly or monthly basis. In addition, you can also use a checklist to exclude specific tables from optimization. However, there is no way of telling which WordPress database table corresponds to a given plugin.
Once you’re satisfied with the configuration, click the Go to Optimizer button to open the page where you can analyze the space left on your database and also start the optimization process. Alternatively, you can enable the one-click link on the WordPress admin bar, allowing you to optimize your WordPress database from anywhere on the admin page.
Key Features
- Remove redundant items such as revisions of posts, trashed posts, spammed comments, unused tags, pingbacks and tracebacks, and many more.
- Optionally excluded a certain amount of recent revisions or those of particular posts from deletion.
- Multisite support i.e., you can configure the plugin to run on the main site and still optimize other subsites.
- Ability to schedule optimization based on different frequencies.
Optimize the database after deleting revisions is a free plugin.
4. WP-Sweep
WP-Sweep is an open-source plugin that enables you to optimize your WordPress database by deleting unnecessary records that may be bloating the WordPress database. Such data includes revisions on posts that are no longer useful, spam comments, orphaned posts, orphaned user meta, and more.
As highlighted earlier, your WordPress progressively builds up junk as your users grow and even as you add more content. For this reason, you need to occasionally get rid of this junk for our database to execute queries optimally. WP Sweep is designed to help you do just that.
To get started with WP Sweep, you need to install it by:
- Downloading and Uploading the wp-sweep folder to the /wp-content/plugins/ directory.
- Activating the WP-Sweep plugin through the ‘Plugins’ menu in WordPress
- You can access WP-Sweep via WP-Admin -> Tools -> Sweep
Unlike other plugins, WP Sweep allows you to choose but does not allow you to select specific database tables that you want to optimize. Instead, you only get to choose between doing a complete “Sweep” of all the tables or cleaning individual tables.
While WP-Sweep does not support scheduled clean-ups, it is the most straightforward and easy-to-use plugin that allows you to get rid of data that may be slowing down your WordPress database. The WP-Sweep plugin uses proper WordPress delete functions as much as possible instead of running direct delete MySQL queries.
Key Features
- Optimize all your databases using the sweep all button.
- Clean individual items under different sweep sections i.e post sweeps or comment sweeps.
- It does an excellent job of locating duplicate data and getting rid of it.
- Simple with an intuitive dashboard.
- No hidden fees or add-ons. It is completely free.
Pricing: The WP-Sweep plugin is free.
5. Plugins Garbage Collector (Database Cleanup)
Plugins are primarily pieces of software that allow you to extend the feature and functionality of your WordPress site. One of the main reasons WordPress is a popular platform is the access to a wide range of plugin features that are easily pluggable into any site. As your site grows, you’re likely to use different plugins from time to time.
While these plugins are great, they often leave orphaned tables and data in your WordPress database table once removed. The Plugins Garbage collector was precisely designed to help you find and delete this kind of data, which, if not removed, could end up slowing down your site.
With the help of this plugin, you can check your database and discover if it is clean or not.
Here is how to can install the plugins and garbage collector:
- Start by Deactivating the plugin if you have the previous version installed.
- Extract the “plugins-garbage-collector.x.x.x.zip” archive content to the “/wp-content/plugins/plugins-garbage-collector” directory.
- Activate the “Plugins Garbage Collector” plugin via the ‘Plugins’ menu in the WordPress admin menu.
- Go to the “Tools”>>“Plugins Garbage Collector” menu item and scan your WordPress database to see if it has some forgotten tables from old plugins.
Key Features
- Allows you to scan the database and show tables created by other plugins apart from the core WordPress database tables.
- Find out database tables left after plug deactivation or deletion.
- Using this plugin, you can check whether your WordPress database is clean.
- You can also view and remove extra tables added to the core WordPress tables.
This plugin is freely accessible.
WooCommerce database clean-up tips
WooCommerce is a customizable open-source platform that quickly allows you to set up an online store where you can sell your products. Using the Woocommerce WordPress plugin, you can quickly turn your WordPress site into an eCommerce site where you can create products, process orders, configure shipping options, and more.
By default, your WooCommerce database uses WordPress database tables and custom tables to store user and order-based customer data. However, storing this data in a separate database is also possible if so desired.
Regardless of the database used, WooCommerce stores tend to generate a lot of data. Over time this data can blot up your database and drastically slow down your website. In this section, we share a few tips that you can use to keep your WooCommerce database optimized and running smoothly:
- Use a database plugin such as WP-Optimize – WooCommerce does not store customer data in dedicated tables. For this reason, tracing data in different database tables can be arduous. A database plugin will help you take care of unused, orphaned, and duplicate data without much hassle.
- Alternatively, you can manually clean up your database using a tool such as phpMyAdmin if you’re well-versed in MySQL databases.
- Remove unnecessary data that is not critical to running your WooCommernce site. Here is some of the data you should consider getting rid of
- Get rid of duplicate data that may be bloating your database.
- Delete Orphaned data – This is the kind of data that is no longer in use by your WooCommerce site.
- Get rid of dangling data left behind by plugins and themes your site no longer uses.
- Leverage plugins such as WP-Optimize to defragment your WooCommerce database and keep it running optimally.
- Always keep the most recent backup of your WooCommerce database just in case something goes wrong during a clean-up, and you need to get back online as quickly as possible.
By following these tips, you can keep your WooCommerce database clean and running efficiently.
To recap, WordPress databases are a vital component in the success of any business and website that wishes to serve many users. While they are mainly invisible, databases need to run optimally for users to have a good experience using your WordPress site. In this guide, we have highlighted some of the best plugins that should help you keep your WordPress database clean and running optimally. However, we also understand that different businesses have different needs, feel free to do more research in case you’re still not sure which plugin is right for your WordPress database optimization needs.
FAQ
How often should I optimize my WordPress database?
Which database is best for WordPress?
Why is my WordPress database so large?
How do I reduce the size of my WordPress database?
Does database size affect performance?
#1 Managed WordPress Hosting
Discover the best WordPress hosting
Try 10Web for free, and enjoy all the benefits of a secure Google Cloud Partner hosting.
