We’ve recently experienced some serious issues with WooCommerce search on eCommerce sites that process a large number of orders. The post meta table needs to be queried when looking for a specific meta_value, which causes a major slowdown. For example, querying the “my orders” section would take 5 seconds or possibly even more, depending on the server environment. To make matters worse, performing searches on the “Orders” screen for orders belonging to a specific email or person can take 30 seconds or more (due to the involvement of extra JOINs on the post meta table).
Both of the above examples are unacceptable for a website with a high amount of traffic and had to be addressed.
Traditional Solutions and Associated Limitations
After doing some research, we’ve found that these issues are traditionally handled in one of two ways:
- Using ElasticSearch indexing, with the help of the ElasticPress plugin, or
- Using a secondary index table within the same site database.
A new solution has been developed, which we’ll go through next, but let’s talk about how it used to be done before.
The ElasticSearch solution sounds good in theory, but our experience leads us to believe that it is not a good thing to integrate with WordPress. This is due to the huge number of differences between the base WP data source (which is MySQL tables) and the ElasticSearch indexes.
While ElasticSearch performs partial searches very well and is pretty good at “guessing” what you intend to write in the search query, these minor benefits are definitely not outweighed by the downsides of integrating these two data sources:
- Latency between the ElasticSearch instance and your hosting provider (note that this won’t be an issue if you have ElasticPress installed on the same server as your PHP environment, but this is not a common scenario with today’s managed WordPress hosting solutions), and
- the number of orders covered in each result (across all pages) is narrowed down to just a couple hundred.
In this case, we would need to query the ElasticSearch instance and then pass on the matched post IDs to WP_Query (while adhering to the max length of the SQL query being sent to MySQL). This breaks the workflow for wide range searches and could potentially provide a misleading number of total results to the store manager searching the Orders screen.
Now, onto the secondary index table. The original solution was presented to us by Patrick Garman, a fellow WordPress developer. He originally intended to improve the “My Orders” section of WooCommerce, but we thought that this fell a bit short of our needs.
Need help with your WooCommerce website? Hire Mitchell and the Saucal team and have them work on your project immediately!
A Traditional Solution to a Traditional Problem
The original implementation of the order index only indexes the Order IDs and the Customer IDs in a secondary table (which is what Patrick set out to fix). His solution is to modify the “My Orders” WP_Query to use his index.
We extended this in a couple of ways:
- We’ve included the customer emails (both the billing email on the order and the customer email, which is derived from the customer assigned to the order). Whenever you search an email in the orders view of the admin section, we trigger the use of the index instead of postmeta.
- We’ve included the customer names (billing, shipping, and display name of the assigned customer).
Here we come across an issue; we don’t want to completely remove WooCommerce’s existing search features, but we need to have some way of using the index conditionally for faster searches. This is because our index doesn’t support Address search, for example, and WooCommerce functionality covers that.
In order to achieve this, we’ve included a parameter like a search. Whenever your search parameter is “name: John D,” we return all orders with names that match John D using a wildcard search. You could search for the Order ID in WooCommerce, but we felt we could make it easier, so with our solution, you can just enter the search term “#1456” and have order 1456 returned to you.
How To Implement It
In order to install this index, all you have to do is install our fork of the plugin and then enable it. Here’s the URL: https://github.com/saucal/wc-customer-order-index
The only complication at the moment is that you need access to WP-CLI to create the initial index. We’re working on enabling an AJAX interface which will allow you to build the index without WP-CLI.
For now, after you enable the plugin, you need to open up your WP-CLI interface and enter the command “wp wc_coi reset_index” for it to start generating the index for you. The time this process takes will vary depending on the number of orders you have on your site.
The HPOS (High-Performance Order Storage) Solution
While in traditional WooCommerce installations, all order data is in the wp_posts table, this resulted in slow site performance as the table grew larger and more complex. HPOS came as a solution to the aforementioned traditional problem to improve site speed by optimizing the data storage process.
With high-performance order storage, order data goes into a separate database specifically made for fast access and querying. As a result, owners of large-volume stores see improved site performance and lower load times.
An added bonus of HPOS is that it can help reduce risk of data loss and improve scalability, making it a huge game changer for larger WooCommerce stores.
Wrapping Things Up
We tried the ElasticSearch solution for one of our customers, and it had too many moving parts for our liking. We prefer something that stays within the scope of WordPress and doesn’t require our customers to sign up for any external services.
To be clear, this is not a problem of ElasticSearch as a technology. It’s just that it’s too different from how WordPress works, so integrating the two created a few downsides that, for us, were deal breakers considering what we were trying to achieve. Additionally, we had to rely on a complex third-party plugin (10up’s ElasticPress).
We iterated on Patrick’s approach and extended it to fulfill our customer’s most common needs. As a trusted WordPress agency, we know that this is not the prettiest solution since it duplicates a lot of the data. We just want to provide our customers with a solid workaround until WooCommerce 3.0 is released and renders this solution unnecessary.
Would you have done something differently? Feel free to send us a pull request on our repository. We’d love to see alternatives!
High fives to our lead developer Matias for helping me with this article.Need help with your eCommerce website? Hire Mitchell and the Saucal team to work on your next project!
