WooCommerce Database Optimization

Is your online store laggy? Does it cause site visitors to bounce off when content doesn’t load properly? You need to optimize your WooCommerce database.

Optimizing your WooCommerce database is important for maintaining site performance and improving the responsiveness of your web pages. If the user’s browser gets a slower response from your website’s server, it will take longer to display content to site visitors when they try to access your store.

This puts out a bad first impression of your brand. Poor user experience leads to site visitors bouncing off your site, sometimes for good.

In this post, we’ll take a look at why the WooCommerce database is slow. We’ll also cover how new, updated custom tables in WooCommerce will help improve website performance.

Why is the WooCommerce database slow?

WooCommerce by default uses both WordPress database tables and custom tables to store data. However, it’s important to note that WooCommerce doesn’t have a dedicated table to store customer data. Customer data is stored in different database tables which makes it difficult to find and retrieve.

WooCommerce stores two types of user data including:

• Order-based customer data. WooCommerce has built-in functionality that automatically stores customer data as soon as an order is created on your online store. Despite the user being a registered user on your WooCommerce site or not, the order-based customer data is always stored along with the order.
• WordPress user data. WordPress user data is also stored but only for site visitors that are registered on your WooCommerce site. It stores data that contains basic customer information such as their email address, date registered, shipping information, and payment details. WordPress also uses this crucial data for a faster checkout process for the user next time they order from your WooCommerce store.

Now, you might be wondering: how do I search the WooCommerce database to find data?

There are many ways to search the WooCommerce database using database queries. It’s important to create your own query based on your specific requirements and the data you’re looking for.

Here are some examples of what WordPress search queries look like:

For user data:

For order-based data:

Since WooCommerce doesn’t store customer data in a dedicated table, it can be quite difficult to find and retrieve the data you’re looking for, especially if you’re not very tech-savvy. The customer data is scattered across different database tables and optimizing the WordPress database becomes challenging.

Database Query Analysis using New Relic

New Relic is a robust web application that offers you a detailed look into the performance analytics of your WordPress site.

It’s a “one-off” solution for monitoring all information related to your WordPress site’s performance. This makes it easy for you to see whether improvements are working and diagnose your site’s performance issues.

New Relic features a “Database” section that gives you an overview of all database tables and types of queries that take the most time on your WooCommerce site. This crucial information can be used to identify and optimize potential “bad” or un-optimized database tables and other files that cause your database to load slower.

New Relic really makes it easier to improve and enhance WooCommerce database performance. This is important because the database can’t keep up with the rate of queries involved with eCommerce sites. This leads to slowness and poor performance of your site overall.

The New Relic dashboard displays database slowness in two ways:

You can see longer SQL queries or queries that take more time to load in the Overview tab.

Using the Databases tab, you can see multiple database tables that take a lot of time to process queries.

You can set the Sort By filter to Most time-consuming and the most problematic database table will be listed at the top.

Overview of WooCommerce data tables

When you install a fresh copy of WooCommerce on your WordPress site, it also installs many different tables directly to your WordPress database.

Let’s take a look at some of the tables that are installed with the WooCommerce installation:

• woocommerce_sessions. This table stores all the data related to customer session data on your website. This includes important data such as cart information and the pages visited.
• woocommerce_api_keys. This table is used to store API keys that are eventually used for the REST API.
• woocommerce_attribute_taxonomies. The woocommerce_attribute_taxonomies table stores your site’s attribute taxonomy names for all products available in your store.
• woocommerce_downloadable_product_permissions. This table stores important permission data for all downloadable products on your WooCommerce site. This tells the browser to let users access the product after they make the purchase.
• woocommerce_order_items. The woocommerce_order_items table stores all the data associated with order items on your WooCommerce site.
• woocommerce_tax_rates. This table stores the tax rate or data you define from the WordPress admin dashboard.
• woocommerce_shipping_zones. This table stores all the shipping zones data you set or create in WordPress.

WooCommerce by default installs a lot of database tables, even the ones you don’t need for your online store. Uninstalling WooCommerce from your WordPress site is easy but it doesn’t fully remove all data tables from your database. In addition to this, it also doesn’t remove any database tables if you simply deactivate the plugin from your site. To remove the database tables, you’ll need to properly uninstall them.

Uninstalling WooCommerce database tables the right way

You can uninstall WooCommerce from your site using two ways. You can remove WooCommerce by going to the All Plugins menu and finding WooCommerce and clicking on the Uninstall button. However, this won’t fully remove all database files, it will still save your settings, orders, products, and pages. To fully clear all WooCommerce tables from your database, you’ll need to remove WooCommerce from your site’s wp-config.php file.

Simply paste this code snippet at the bottom of your code to fully uninstall WooCommerce from your site:

define( 'WC_REMOVE_ALL_DATA', true );

For a more detailed explanation of this, you can take a look at this complete guide on installing and uninstalling WooCommerce from your WordPress site.

To clear up unused data from your WooCommerce database without having to uninstall WooCommerce, you can use a WordPress optimization plugin like the WooCommerce Prune Orders plugin. The plugin offers an all-in-one solution for deleting old order data from your WooCommerce site. This is a great way to enhance website performance and makes it easier to optimize your WordPress database without coding.

It’s important to note that you shouldn’t delete completed orders from your WooCommerce site. However, if you have many failed or canceled orders on your site, using a plugin such as WooCommerce Prune Orders can be useful.

The plan for the new custom tables implementation in WooCommerce

Over the years, WooCommerce has constantly been updating its code and making improvements to enhance the web performance of online stores. Now the developers at WooCommerce are planning to implement custom tables and replace them with the legacy, chunky tables that make the WooCommerce database slow.

These “new” custom tables will help improve WooCommerce database performance and make it easier to remove and edit unused database tables. The new custom tables will achieve three things including:

• Scalability. WooCommerce aims to make it easier for all sizes of online shops to scale without needing additional plugins and software. This is done by implementing dedicated custom tables for main WooCommerce features. As a result, you’ll have cleaner database tables that are easier to manage.
• Simplicity. The new tables also aim to make it easier and much simpler to retrieve data from your WooCommerce database. It will be easier to modify and make changes to your WooCommerce site without having to go through tons of useless database tables.
• Reliability. WooCommerce’s new custom tables will also make it easier to take and restore targeted backup data, set read and write locks, and prevent race conditions for your data.

For a better understanding of the new aims and plans for WooCommerce custom tables, you can go over this write-up on the plan for the WooCommerce custom order table.

Proposed custom WooCommerce database tables

Let’s take a look at some of the proposed custom tables that will be a part of core WooCommerce by Q3 2022:

• wp_wc_orders. Most of the WooCommerce order data and important meta keys will be stored here.
• wp_wc_order_addresses. This table will contain all the address data related to user orders. This can include shipping and billing information for orders on your WooCommerce site.
• wp_wc_order_operational_data. The wp_wc_order_operational_data table stores fields and flags for maintaining the internal order operations state.
• wp_wc_orders_meta. This table will store all extension data related to orders.
• wp_wc_order_notes. Instead of using multiple tables, the wp_wc_order_notes stores all store order notes.

WooCommerce Custom Order Tables

WooCommerce creates 40 different post meta entries for every order you receive on your online store. This way, the more orders you receive, the larger-sized tables will be created in your WooCommerce database. As a result, it slows down queries and eventually reduces page load speed on the front-end.

With the new WooCommerce custom order tables, there’s no such thing as 40 separate entries for each order. Instead, you have a more optimized order table that takes up one new row per order.

While this is certainly a solution, it is better to wait until WooCommerce rolls out their custom tables implementation.

Why custom tables will make a huge difference when it comes to performance

The new, updated custom table’s main purpose is to boost the scalability of stock WooCommerce. This is especially useful for larger-sized stores that have a lot of products as their database tables are much larger in size as compared to a smaller store with fewer products. In addition to this, the custom tables will also be great for stores that are looking to expand or grow their operations in the future.

Custom tables will help other plugins and themes store data better in your WooCommerce database. This way, you don’t create a mess of your database tables while still being able to find the data you need. For instance, the product_total_amount and product_tax_amount will make it easier to store tax-related data for products as such fields were not stored previously.

Conclusion

Optimizing your WooCommerce database is important, especially if your store has many products and receives a lot of orders. This can cause an influx in queries and create larger-sized database tables. As a result, all of this slows down the performance of your online store and causes a poor shopping experience for customers.

This is why it’s important to use the new WooCommerce custom tables to optimize your WordPress database.

Before you begin fixing your WooCommerce database, it’s important to use a solid database diagnostic service such as New Relic to identify potential errors and bugs causing your site to load slower.

Did you know that the Shoptimizer theme helps make your online store look attractive without affecting the performance of your WooCommerce database? Shoptimizer is lightweight but features all the functionality you need to make your WooCommerce site look and feel unique.