Everything You Need to Know About the WooCommerce Database: How It Works, Schema, and More
A database is a critical tool in eCommerce that can help you keep track of data, making it easier to access, but did you know that your WooCommerce store has its own database that can help you optimize your store for better performance and troubleshoot issues?
The WooCommerce database is a critical part of your eCommerce website, and while it can help business owners access data from orders and goings-on of your store, it can be difficult to understand its numerous components.
In this article, we will explore what the WooCommerce database is, how it works, and how you can access and optimize it to serve your needs.
What is a database?
A database refers to a system where information is stored in an ordered way. A database contains tables with rows and columns to look up information easily. WooCommerce uses a type of programming language known as Structured Query Language (SQL), commonly found in databases, and the WooCommerce database uses the same MySQL database as WordPress, which are a collection of tables that stores all of your product, customer, and order data and can be used by all aspects of your online store – from product pages to the checkout process and everything in between.
Why should you understand your WooCommerce database?
As an eCommerce business owner, knowing how your WooCommerce database stores all the important information for your online store ensures you have a greater understanding of your site.
The WooCommerce database stores a wide variety of essential but sensitive information, such as customer data and order details. Having an understanding of your database is vital because when the database is not properly secured, it can be vulnerable to hacks and data breaches, which will wreak havoc on your website and impact your trustworthiness as a business. Understanding how to manage the database can help you take the critical steps to secure the data and prevent unauthorized access.
Understanding the WooCommerce Database Schema
WooCommerce uses both WordPress database tables and its own custom tables for data to provide functionality for website administrators. As with general databases, each table contains rows that represent individual pieces of data and columns that represent different attributes of each piece of data. There are two main types of tables in the WooCommerce database: a product table and an order table.
For example, a product table would contain the key columns such as title, description, and rows for each individual product and may look like the following tables:
| Title | Description | Price | Quantity |
| T-shirt | Black short-sleeved T-shirt | $17.99 | 1 |
| Sweater | Grey V-neck | $24.99 | 1 |
An order table can contain a variety of information about each order, such as the date placed, status, order ID, shipping methods, shipping zones, product variation information, payment tokens, tax classes, and other variants of metadata, and may look like this:
| Item | Date Placed | Status | Order ID |
| 5.1 Surround Sound System | 2023-04-25 | Shipped | 123456 |
| Travel Alarm Clock | 2023-05-18 | Dropped | 224466 |
How Do I Access My WooCommerce Database?
The WooCommerce database is a MySQL database, and many WordPress hosts offer phpMyAdmin as a graphical user interface to view and access the database and are typically cheaper hosts using cPanel, but advanced users can use WP-CLI to execute SQL database queries. While these components are, on the surface, quite technical and difficult to follow, if you ever find yourself overwhelmed, you can reach out to your developer or get help from us at Saucal: we’re WooCommerce experts, and we support clients through all Woo-related matters.
SQL Queries
While experts and developers can easily access SQL queries, understanding this can make the process simpler further down the line, even if you are not an expert. The WooCommerce database uses MySQL, and you can access important data from the database and any new tables you have created by writing and executing common MySQL queries, which we will show below.
Please note that many WordPress hosts or WordPress security plugins change the WordPress database prefix to protect the WordPress setup against hackers, so if your WooCommerce site has a different prefix on the database other than “wp,” you will also need to change this in the examples below.
Orders
Currently, the WooCommerce database uses a custom post type “shop_order” to store orders, so you will find orders in wp_posts under the post_type column set to shop_order. To load orders, use the following query:
SELECT *
FROM wp_posts
WHERE post_type = ‘shop_order’
Products
You can access the WooCommerce products stored in your WooCommerce database by using SQL queries to retrieve the data from the database. For example, here’s how you could retrieve all products in your WooCommerce store:
SELECT *
FROM wp_posts
WHERE post_type = ‘product’
AND post_status = ‘publish’
This SQL query will return all the products in your WooCommerce store that have a post type of “product” and a post status of “publish.” You can also access the product data using the WooCommerce REST API, which provides a way to retrieve data from your WooCommerce store using HTTP requests.
Soon, this will change to a separate table to enable High-Performance Order Storage (HPOS), which you can opt into in WooCommerce 7.1. Learn more about HPOS here.
You should note that it is important to back up your database before making any changes, as alterations to the WooCommerce database can cause many functional issues with your online store, such as PHP errors, missing links, or a slower website speed.
To further optimize your WooCommerce operations, understanding and implementing webhooks can automate tasks such as inventory updates and order notifications, making your store even more efficient. Integrating webhooks properly ensures that your database and store operations run smoothly, keeping your eCommerce site fast and responsive to real-time events.
User Data
WooCommerce customer data is not centralized. It can be distributed among WordPress tables that are customized for the platform itself or based on the same solutions as every WordPress site. This makes it difficult to find what you are looking for, but there are ways around these issues:
Finding Customer Information by Order or With WordPress User Data
You can find the customer information you need to look up either by order or with WordPress user data. As the customer data is added to the database every time they make an order, user details end up being duplicated. When searching by order ID, you can find customer data by searching the wp_postmeta table using that ID number, as much of the related order data is stored as post meta.
What Should I Look Out For When Managing My WooCommerce Database?
Managing your WooCommerce database may seem like a big task, but there’s one simple thing to remember: make sure that, if you’re using a WordPress plugin to manage your database, it’s a reputable one! While many plugins like WP PHP MyAdmin are recommended, they can cause more harm than good in many cases!
High-value and high-frequency stores should be especially careful with plugins interacting with the WooCommerce database. For example, if you use a plugin that automatically optimizes your database every time it syncs with WordPress, this could create duplicate records, which may cause duplicate orders!
WooCommerce’s usage of the WordPress database is both a blessing and a curse. It needs expert care, but if you feel overwhelmed by any technical task regarding your database, don’t hesitate to reach out to us at Saucal to avoid critical errors or downtime.
The WooCommerce database works well out of the box at scale until you start attempting to play wack-a-mole with database performance issues.
A common solution that non-experts do is to add extra indexes on fields to improve read speeds. This helps reading from the database but hurts write speed. And you know what part makes a ton of writes to the database? Checkout! So you’ll be gaining speed there in one aspect, but losing performance on one of the most important aspects of your site.
Instead of indexing fields that are not meant to be indexed, either use alternative lookup tables (WooCommerce does this until they implement the High-Performance Order Storage) or query a larger amount of data and then filter it in PHP.
In order to be prepared to get the benefits of a better table structure, be sure that your plugins use the CRUD API (introduced back in 2017), instead of the WP Post Meta API. This will guarantee compatibility moving forward, and your plugin being able to query data in the most performant way available.
Matias Saggiorato, CTO of Saucal
How Do I Delete My Entire WooCommerce Database?
If you are running a WooCommerce store, you may need to reset it because you’re changing your hosting provider, rebranding your business, or you feel like it’s time to start afresh. Whatever your reason, deleting your store is a relatively easy process, and it only takes a few seconds to do. This is why you should not be hasty when it comes to removing your database.
Remember, deleting your entire WooCommerce database will cause every aspect of your products to disappear from the record. Your products, orders, item information, and everything else will be lost forever. Therefore, it should only be done if there’s no other option available!
If you do decide to delete your entire WooCommerce database, follow these steps using cPanel and phpMyAdmin (or a “workaround” plugin like the one shared above):
1. Go to phpMyAdmin
2. Select the wp_woocommerce table
3. Click on “Drop Table”
4. And just like that, your WooCommerce database has been deleted!
Remember, while it is easy to delete your WooCommerce database, you have to ask yourself why you want to do this because once it is done, you cannot reverse it.
Work With Saucal on All Things WooCommerce
Understanding the WooCommerce database is important for running a more efficient and effective online store. Whether you need to scale up your business or learn how to use information better, your WooCommerce database is a treasure trove of information.
Many eCommerce business owners think that the technicalities of utilizing the WooCommerce database properly keep them from understanding it, but they don’t have to be developers to get access to their database and make improvements to their WooCommerce store.
Whatever your concerns, We’re here to support and offer expert tips and put them into action, so you don’t just get to grips with the technical components, but you use this information to gain a true competitive edge!
Whatever you need, reach out for help managing your WooCommerce store with ease. Get in contact with us today!
