Introduction In the fast-paced world of e-commerce, delivering an exceptional shopping experience is paramount for enterprise clients. Hellenic Technologies, a leading technology solutions provider, has found outstanding success in leveraging WooCommerce HPOS (High-Performance Order Storage) to meet the demanding requirements of their enterprise clients. In this article, we will explore how Hellenic Technologies utilizes WooCommerce HPOS to optimize cart and checkout processes, improve search functionality using metadata indexes, showcase the significant performance improvements, and highlight the remarkable database size reduction achieved through its implementation.

Understanding WooCommerce HPOS

WooCommerce HPOS is an advanced solution designed to enhance the performance, scalability, and efficiency of the e-commerce platform. With its high-performance order storage capabilities, HPOS allows enterprises to process orders at a rapid pace while maintaining data integrity. Its optimized architecture and indexing mechanisms enable faster searches and significantly reduce database size, making it an ideal choice for large-scale online stores.

Optimizing and Testing Order Creation with HPOS

Hellenic Technologies recognized that the cart and checkout stages play a crucial role in customer satisfaction. Through careful analysis and iterative improvements, they leveraged WooCommerce HPOS to optimize these processes, resulting in faster and more efficient transactions.

This optimization resulted in a significant improvement in order insert performance, with approximately 5 times faster execution. The improvement is mainly attributed to the reduction in insert queries due to the flattening of multiple meta fields into custom tables.

Result:

[HPOS] Time to create 1000 orders: 15.18 seconds
[Posts table] Time to create 1000 orders: 78.12 seconds

These results clearly demonstrate the significant performance gain achieved by leveraging HPOS for order creation. The use of custom tables in HPOS reduces the number of insert queries required, resulting in faster execution and improved efficiency.

Optimizing and Testing Checkout Creation with HPOS

In the HPOS project, we have the advantage of implementing indexes on the order_meta table, which was not possible with the post table. The addition of indexes significantly speeds up the process of searching orders using metadata. In this particular test, the performance improvement is approximately 10 times faster.

Result:

[HPOS] Time to search 1000 orders by metadata: 0.05 seconds
[Posts table] Time to search 1000 orders by metadata: 0.64 seconds

These results clearly indicate the significant performance gain achieved by implementing indexes on the order_meta table in HPOS. By optimizing the lookup process for metadata queries, businesses can efficiently search and retrieve orders based on specific metadata criteria.

Filtering by an Indexed Column with HPOS

With the introduction of flattened post_meta keys into separate columns, we have also optimized our indexing strategy. In particular, we have implemented indexes on relevant individual meta_keys to improve query performance. A notable example is the customer_id field, which now has its own indexed column in HPOS, whereas in the post table it was stored under the _customer_user meta key. As a result, retrieving all orders for a customer in HPOS is now 40 times faster, thanks to the utilization of the index on the customer_id column.

Result:

[HPOS] Time to filter 1000 orders from customer: 0.01 seconds
[Posts table] Time to filter 1000 orders from customer: 0.59 seconds

This improvement demonstrates the effectiveness of leveraging indexes for faster and more efficient filtering based on specific columns.

Search orders using non-indexed column with HPOS

Similar to the previous optimizations, there are some flattened columns in our implementation that are not indexed. However, despite this limitation, we anticipate performance improvements due to the reduced search area. Previously, the search encompassed the entire wp_postmeta table, whereas now it is narrowed down to a single entry per order. As a result, we have observed improvements of up to 3 times in our database performance for the below query.

Result:

[HPOS] Time to search orders: 0.30 seconds
[Posts table] Time to search orders: 1.00 seconds

These results highlight the advantages of flattening the columns and narrowing down the search area.

Database size optimization with HPOS

According to data from woocommerce.com, the database size in HPOS is effectively reduced by sharding the post-meta table and moving out all entries related to orders. This approach allows site administrators to potentially remove existing post meta rows belonging to orders, resulting in improved site performance outside of the orders workflow. By reducing the table size, there are fewer rows for MySQL to scan during queries, leading to faster and more efficient performance. For example, on woocommerce.com, which is a large deployment, orders account for approximately 81% of all post records. In the wp_postmeta table, this skew is even more pronounced, with meta records belonging to orders making up about 97% of all rows. With the plan to remove post meta records specifically related to orders after HPOS has been running for some time, woocommerce.com expects a reduction in the post meta table data by 97%. This substantial size reduction is anticipated to have a positive impact on overall site performance, not just limited to order-related activities.

Conclusion

Hellenic Technologies' successful implementation of WooCommerce HPOS has demonstrated the significant advantages it offers to enterprise clients. By optimizing cart and checkout processes, improving search functionality using metadata indexes, achieving remarkable speed improvements through filtering by indexed columns, and reducing database size by 97%, Hellenic Technologies has showcased the power and effectiveness of leveraging WooCommerce HPOS for large-scale e-commerce operations. With its enhanced performance and scalability, WooCommerce HPOS proves to be a valuable solution for enterprises striving to deliver exceptional shopping experiences to their customers.

Note: This article is based on real-world examples and benchmarks conducted by Hellenic Technologies. Results may vary depending on specific implementations and configurations.