Today, we’re pleased to announce the beta of our Custom Products Tables plugin that’s designed to improve the performance of WooCommerce stores by adding database tables that are designed for eCommerce.
This builds on the investment we made in 3.0 which introduced Data Stores and CRUD functionality that provides a uniform way of accessing a store’s data regardless of where it is saved.
Why products first? Simply because it’s one of the most complex Data Stores to change, and one which can positively impact stores of all sizes.
The plugin replaces the current WooCommerce product Data Store with one that makes use of brand new, normalised, dedicated product tables.
During our tests we set up two identical stores on the same hosting provider with the same theme and WooCommerce settings, the only difference being one was running the Custom Products Table Feature Plugin.
To test the new product tables we needed to easily create a lot of store data – specifically products, orders, and customers. To do that, we created the WooCommerce Smooth Generator – an easy to use development plugin which allows you to generate WooCommerce data to test the scalibilty of any of your development.
For the tests, we created one data set of 500 products and imported that into two identical stores with Storefront as the theme and no other plugins – one store with the normal WooCommerce data structures and the other with our new feature plugin.
Both stores also had 70,000 orders in the database and meta data in the range of 1.4 million rows.
The results, so far, have been great – with improvements of up to 30% on page load times! Checkout, arguably the most important part of the store experience, has seen the biggest performance gains.
Installing the plugin
To install the plugin you will need :
- Custom Products Table Feature Plugin : Download the Version 1 release.
- WooCommerce 3.5 Dev : You’ll need to switch to the master branch of WooCommerce.
- WP-CLI : To migrate products to the new data stores, you’ll need WP-CLI.
During the beta period we’ve limited access to the plugin’s functionality so that we can gather feedback from our developer community.
We do not recommend that you install and use this on a live store.
There are two WP-CLI commands available at present to migrate your data:
wp wc-product-tables migrate-data [--clean-old-data] – This command will migrate data from the current WordPress tables to the new tables, passing the –clean-old-data flag will result in the old data being removed from the WordPress tables. Please be cautious when using this flag as it can result in you loosing your product data if not backed up and you need to revert.
wp wc-product-tables recreate-tables – This command will drop all the new product tables, removing all data within them and then create new clean tables. This command is useful for testing the migration process from fresh again should you wish. Again, please be cautious as if you ran the migrate-data command with the –clean-old-data flag, running this will result in all your data being lost, so always keep a backup on hand of your products.
Do note that any new products created after you have enabled the plugin will be created in the new tables and will not be available if you disable the plugin again.
What tables are added
- wc_products – Contains product data eliminating the majority of meta data table storage. A few fields in the meta data table not accessed often, and all extension specific data, will stay in meta data.
- wc_product_attributes – Contains product attributes and their settings.
- wc_product_attribute_values – Contains product attribute values and their settings.
- wc_product_downloads – Contains product downloads and their settings.
- wc_product_relationships – Contains all product relationships to other products or objects within the WooCommerce ecosystem, i.e. other products, images etc.
- wc_product_variation_attribute_values – Contains variation data for products.
These tables currently cover all aspects of products, including product settings, attributes, variations, downloads, as well as other relationships such as up-sells, cross-sells, grouped products, and images.
Compatibility with extensions and themes
We’ve kept backwards compatibility in mind by adding specific functionality that checks meta data calls and then intervenes to ensure the data gets routed to and from the correct tables.
Whilst we have taken a care in ensuring developers that are not using CRUD can get/set data in the correct tables, we do advise developers with plugins and themes that make direct meta calls to avoid that and rather use the getter and setter methods provided by the Data Stores since version 3.0. We’ve published documentation here to help you move to the CRUD system.
Our next milestone is to get this plugin released on WordPress.org so that any store can benefit from the performance improvements. To get there, we need your help putting the beta through its paces so we can catch all issues and better understand the performance improvements and problems you’re seeing in the wild.
Once you’ve installed the beta, please open an issue for any problems you encounter on the GitHub repository. And, let us know in the comments what performance improvements you’re seeing – we’d love to hear from you!
We plan to include the new product tables in a major version update early next year.