This post originally appeared on Codeable.
We’ve recently experienced some serious issues with WooCommerce search on eCommerce sites that process a large number of orders. The postmeta 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 5s 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 30s or more (due to the involvement of extra JOINs on the postmeta table).
Both of the above examples are unacceptable for a website with high amount of traffic and had to be addressed.
Current Solutions And Associated Limitations
After doing some research, we’ve found that these issues are currently 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.
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 datasource (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 just improve the “My Orders” section of WooCommerce, but we thought that this fell a bit short for our needs.
We know that his current implementation is just meant to be a temporary fix until the release of WooCommerce 3.0 and the huge database revamp that is planned for it (which will fix the issues we are discussing in this post). We also know that WC 3.0 is not slated to be released until mid-2017, and since we need to make our customers happy before then, we forked Patrick’s implementation.
Our Proposed Solution
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 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.
How Things Have Changed
With the use of this plugin, we have been able to vastly improve the performance of our customers’ large stores. Here above you can see an example where the time was up to almost 40 seconds. In one of our worst performing cases, email search on WP-Admin was taking over 50 seconds.
Using this plugin, we reduced the search time to less than 5 seconds, improving it by 867%!
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 different? 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.
