Practically every website on the internet stores information from the website on its databases. These repositories of information are then accessed using queries, whenever a visitor loads up the website. The page upload time depends on how fast these queries are fulfilled, which in turn drives site visits and page rankings.
As an ecommerce website owner, databases are going to be a major part of your site upkeep. Database optimization refers to speeding up the process of data retrieval, which consequently will cause the page to load quickly. It involves minimizing the data stored, by deleting metadata such as comments, post revisions, trash and the like; anything that is not directly related to the page performance is eliminated.
There are three different aspects of database optimization:
- Database Design
- Database Administration
- Database Analysis
Here we shall focus primarily on database analysis and administration.
The WooCommerce database in WordPress is where all of your data regarding the WooStore is saved. This includes literally everything from products, posts, comments, trackbacks, and pingbacks to inventory, orders, taxes, taxonomies, and payment sessions.
Managing and optimizing this database can be done in two different ways:
- Optimizing the Database using phpMyAdmin
- Using a database optimization plugin
The former option can be attempted if you are at least familiar with PHP and know your way around the Control Panel. If that doesn’t seem like your cup of tea, not to worry; optimization plugins work just as fine!
Wait a minute though. Before we proceed to optimizing databases, we need to backup our existing ones first! This gives us something to fall back on in case there is some problem during optimization. If we skip the backup, we might face a complete breakdown of our website. Now that won’t do, will it? Even if you are working on your staging site, before going live, it is always a good idea to backup databases frequently.
So, first things first.
Backing up Your WooCommerce Database
There are plenty of backup plugins out there to choose from, depending on the functionalities you require.
The most important aspects of backups include quality of the backup, ease of restoration, and multi-location support. All of these individually contribute to insuring your site against natural and unnatural web-disasters.
We do not want to drift too far from our main goal of database optimization, so suffice to say that WooCommerce can be easily backed up by using a backup plugin like BackWPup, VaultPress, or BackupBuddy. You can check out the different plugin comparisons and reviews before you pin down the appropriate one for your website.
Now that the backups are done, it is time to move on to the meat of the matter i.e. optimizing our database. You can selectively determine which aspects of the database need to be optimized or run a complete optimization process as a whole.
Which leads us to the first method of database optimization, using phpMyAdmin.
Database Optimization using phpMyAdmin
As WooCommerce is a child of WordPress, it makes use of several of WordPress’ database tables (with certain differences), along with adding its own.
As you are aware, WordPress uses MySQL as its database management system and PHP as its scripting language. When we first install WordPress, we are asked to provide a Database name, apart from the username, host, and password. By default, this is ‘wp_database table name’. The moment the installation is complete, 11 tables are added automatically to your database, which store comments, links, site settings, posts, post types, taxonomies, users, and user information.
WooCommerce uses all of these, with one major change; the posts table used by WordPress for Custom Post Types is used by WooCommerce for storing Products, Orders, Hooks, and Coupons.
In addition, it also adds some new tables to your database that store information about product sessions, product permissions, tax rates, locations, shipping zones, and payment tokens.
You can access and modify these tables by logging in to your phpMyAdmin control panel, under the Database section.
New tables are added every time you install a new plugin on WordPress, each with their individual tables. Oftentimes, when you deactivate and uninstall redundant plugins, some of these tables are still stuck in the database. You can delete these unnecessary tables from the phpMyAdmin interface, but only once you make absolutely sure that those do not affect the working of your existing plugins.
Specifically for WooCommerce, if you simply want to optimize all of the tables without deleting any data, you can follow this process:
- Go to Databases in phpMyAdmin
- Select the ‘Check All’ option under the WooCommerce Tables list
- Click on the ‘With Selected’ dropdown menu next to the Check All option
- Select ‘Optimize’ and let WordPress do the rest
This is by far the easiest method of optimizing your database if you do not want the bother of installing an additional plugin. You can further revise the number of Post drafts saved, change the number of days the Trash is stored for, and limit the user information that is stored in the database. This will require some in-depth knowledge about
MySQL and PHP though, more so that you do not delete important files in a fit of optimizing the lot!
Tip: Never use the ‘Drop Table’ option while managing tables. It will delete your table irrevocably.
The alternative is to use a database optimization plugin, which is what we will be seeing next.
Database Optimization using an Optimization Plugin
Optimization plugins are a wonderful resource as they analyse and handle the entire optimization process by themselves and even offer suggestions as to what might be taking up space on your site, apart from databases. Quite the one-plugin-army!
Here is an overview of the best plugins for WooCommerce database optimization.
The WP-DB Manager lets you backup, optimize, repair, and restore your database successfully, on your website. The plugin gives you a list of your databases with their sizes, so that you can determine what exactly is loading your website and manage it accordingly. Backups can be extracted directly or as compressed files, and can be scheduled too.
Quite like the tables in the control panel, you need to select the WooCommerce database that needs to be optimized.
One of the plus points of DB Manager is that it allows you to repair your databases too. For example, if at any given point of time a corrupt database gets activated, due to which products and other options are not visible on the screen, you can simple repair the database to get it back to normal.
WP-Optimize is THE most popular plugin for database management on WordPress. It has a very clean and user-friendly interface which makes navigating across the plugin very easy. It comes equipped with an option to add a direct link to the main administration panel so that you can quickly check the status of the databases. The best feature of WP-Optimize is that it applies all MySQL commands to your database tables without using phpMyAdmin, which makes it very easy to use, especially for us non-coders!
Compatible with cloud backup options like Google Drive, Dropbox, Openstack Swift, DeamObjects, Rackspace Cloud and more, UpDraftPlus backs up your databases at multiple locations, across various servers. Storing backups on servers other than your main hosting server is important because, in case your main server crashes, you can pull up the backed up database form the other servers.
Another advantage of the UpDraftPlus plugin is that it allows you to back up databases and other files separately, with different schedules. This is especially useful for WooCommerce store owners, as we might update the products or product details several times, in which case separate backups can be highly expedient! These backups can also be encrypted for security.
WP Clean Up Optimizer
WP Clean Up Optimizer is a freemium plugin which, true to its name, handles the sweep-through of your website completely. A straightforward plugin, it shows a list of the redundant data right away on the dashboard and enables a single-click deletion process which is very convenient. Critical tables are highlighted in red so that you do not delete them accidentally. Also, the WP Clean Up Optimizer lets you track users who are online or are logging in, as well as block them if necessary. All of these options are included in the premium version; the free version has very limited applications.
WooCommerce databases tend to increase by leaps and bounds, especially as your store starts to grow. These can occupy significant space on your web-hosts and strain your hosting plans, besides increasing your site upload times. The line that separates useful data from the redundant can be quite thin for databases and walking it successfully is difficult.
Database optimization is a necessary chore, especially if your site is hosted on shared hosting plans. Managed hosting eases this somewhat, since they take care of all your backups for you. Nevertheless, it is good to know what exactly database optimization entails and how can we manage it.