WooCommerce 2.7 Beta One was released yesterday. This release is the realisation that WooCommerce has grown so rapidly that it’s now dwarfing the very product it was designed as a plugin for – WordPress. Here at Lime Web Development we’ve helped many customers large and small with WooCommerce’s performance issues that have plagued the 2.5 and 2.6 releases. Performance issues usually trickle down to WooCommerce’s use of the WordPress functions for storing product data. This usually means the posts and postmeta tables become huge and unwieldy – even on powerful hardware. We have to get creative with caching, exotic database engines and creative hardware design.
Many of the WooCommerce & WordPress functions end up with complex SQL queries with many JOIN and GROUP statements. For example, the SQL query for “get me 5 random featured products” – usually from a shortcode like this [featured_products per_page=”5″ columns=”5″] will result in a 27 line SQL statement with 2 INNER JOINS, 8 WHERE clauses, rounded off with a GROUP and an ORDER BY. This results in the creation of a temporary table and a file sort.
All that for just rows containing one column.
A DBA would jump around and flail their arms, a little steam my even come out. On a small store, this would probably go unnoticed. At Lime, we typically see postmeta tables containing 2 million rows. This type of vertical meta data does not scale.
Another example would be the term count – the numbers in brackets after your category list in the Layered Nav Widget – 4 INNER JOINs, 1 LEFT JOIN, 6 WHERE clauses and finally a GROUP BY – 33 lines of SQL for a few numbers out of the database.
We see two solutions for this problem;
- A Magento style EAV or horizontal meta data.
We don’t use Magento, however it’s database design, whilst not perfect (What is?) could be a good reference. - Splitting postmeta out into smaller tables.
The WooCommerce team has chosen this approach.
The core WooCommerce team have realised this and 2.7 leads the way for some serious data redesigning that we hope for are only the start of whats to come in the future release.
We are teased with suggestions of “speed improvements of around 94%”. Which sounds very promising.
Will WooCommerce 2.7 solve all our scalability problems?
No, we don’t believe it will. However it is a step in the right direction for solving the problems we face with large WooCommerce stores.
