An introduction to WooCommerce database optimisation

Your database is the backbone of your WooCommerce powered store. It not only stores essential information, but also plays a major (and often unsung) role in determining the speed, efficiency and overall performance of your store.

Where milliseconds can mean the difference between a sale and a lost customer, ensuring your database is running at optimal levels is a business necessity.

Navigating the complexities of database optimization can be daunting, especially when dealing with a system as complex as WooCommerce.

In this post we’ll get into some of the common database performance issues that might be silently hampering your store’s potential and how to resolve them.

Understanding WooCommerce database structure

WooCommerce inherits the WordPress database structure but extends it significantly to cater for e-commerce functionalities.  

At its core, WooCommerce utilises several standard WordPress tables:

wp_posts: This is where all types of post data are stored, including WooCommerce products, orders, and shop coupons. Each item is stored as a different post type.

 wp_postmeta: This table accompanies wp_posts. It stores additional information (meta-data) about posts, like product pricing, inventory, and other product-specific details.

WooCommerce introduces several custom tables to efficiently handle e-commerce data:

wp_woocommerce_order_items: This table stores line items for orders. Each entry is associated with an order in the wp_posts table and includes details about products, taxes, shipping, and fees.

wp_woocommerce_order_itemmeta: Similar to wp_postmeta, this table stores meta-information about order items, such as quantity, subtotals, and tax data.

One of the unique aspects of the WooCommerce database is its heavy reliance on metadata. While this allows for greater flexibility (you can virtually store any kind of data), it can also lead to performance issues. Understanding how to efficiently query and manage this metadata is important to optimise your WooCommerce database.

Common database performance issues in WooCommerce

Hosting environment

Web hosting is not a commodity, and not every web host is created equal!

The performance of your WooCommerce database is hugely influenced by your hosting environment.

Many cPanel-based shared hosting platforms or servers with limited resources will struggle to keep up with the demands of a busy WooCommerce site.

Specialist solutions such as our WooCommerce Hosting come with the specs and features you need to manage a growing store.

Key considerations:

• Server specifications and resources.
• Database-specific configurations and limitations.

Large volume of data

As your WooCommerce store grows, so does your database. A large volume of products, orders, and customer data can lead to a bloated database.

This bloat can significantly slow down your website, as more time is required to query and retrieve data.

Key culprits:

• Extensive product catalogues.
• High volume of completed, cancelled, or pending orders.
• Accumulated customer data over time.

Inefficient database queries

Inefficient or poorly written queries can drastically reduce database performance. Queries that are not optimized can take longer to execute, increasing page load times.

Common issues:

• Queries that don’t make good use of indexes.
• Complex JOIN operations on large datasets.
• Redundant or duplicate queries on the same page.

Excessive use of Postmeta

WooCommerce relies heavily on the wp_postmeta table for storing additional information about posts. Overuse or mismanagement of this table can lead to performance issues, especially if the table becomes excessively large.

Typical problems:

• Storing large amounts of data in the wp_postmeta table.
• Frequent querying of the wp_postmeta table without proper indexing.

Unoptimized WooCommerce settings

Certain WooCommerce settings and features can inadvertently impact database performance if not properly configured.

Examples:

• Product reviews leading to a high number of comments.
• Extensive use of WooCommerce widgets on numerous pages.

Plugin and theme conflicts

Third-party plugins and themes can sometimes create unexpected database performance issues. Poorly coded plugins or themes might create unnecessary database queries or load additional resources, slowing down your website.

Issues to watch for:

• Plugins that create extensive database logs.
• Themes that make heavy use of database queries for dynamic content.

Best practices for database maintenance

Routine clean up

Over time, databases accumulate data that is no longer relevant or necessary. Regularly cleaning up this data can significantly improve performance.

Cleaning old customer sessions

• Access your database via phpMyAdmin.
• Run a SQL query to delete old sessions from the wp_woocommerce_sessions table.

Removing old, completed orders

• Create a backup of your database.
• Use a plugin like “WP-Optimize” or custom SQL queries to remove orders that are over a certain age.

Clearing out expired transient

• Install a plugin like “Transients Manager“
• Use it to view and delete expired transients from your database – What are Transients?

Managing post revisions

WordPress automatically saves revisions of your posts and pages. While useful for restoring previous versions, too many revisions can bloat your database.

Limiting post revisions

• Edit the wp-config.php file.
• Add the line define(‘WP_POST_REVISIONS’, 5); to limit revisions to 5 per post.

Cleaning up old revisions

• Use a plugin like “WP-Sweep” to remove old revisions.
• Alternatively, run a SQL query in phpMyAdmin to delete revisions older than a certain date.

Handling orphaned metadata

Orphaned metadata refers to data that is no longer linked to an existing WordPress post, often due to the deletion of the post. This data can accumulate and slow down your database.

Identifying orphaned metadata:

• Run a SQL query in phpMyAdmin to find orphaned entries in the wp_postmeta table.
• Query example: SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

Deleting orphaned metadata:

• After confirming they are orphaned, delete these entries with a SQL command: DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

Automated tools and plugins

Several tools and plugins are available to help automate the process of database maintenance. Tools include:

• WP-Optimize: A plugin for cleaning up your database.
• WP-DBManager: Allows for database optimization, repair, and backup.
• Advanced Database Cleaner: Focuses on removing orphaned and duplicated data.

Regular backups

Before performing any maintenance tasks, it’s crucial to have a recent backup of your database. This ensures that you can restore your site in case anything goes wrong during the optimization process.

• Using plugins like UpdraftPlus or WPvivid for automated backups.
• use your hosting provider’s backup solutions. 20i WooCommerce hosting customers get free daily backups as standard and you can take manual backups any time.