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:

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.

Need help with your eCommerce website? Hire Mitchell and the Saucal team and have them work on your project immediately!

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:

  1. 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.

  2. 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

WooCommerce-search-before

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.

WooCommerce-search-after

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.

Need help with your eCommerce website? Hire Mitchell and the Saucal team to work on your next project!

Quality: The Codeable Differene

  • rahilwazir

    Very helpful, thanks for sharing the code as well :)

    • Thanks Rahil, you’re welcome!

  • Hey guys, I think the HTML is broken in this article. Everything starting from “Our Pr” looks like an unclosed header tag styling.

    • Hey Leho,
      yep unfortunately something broke up and we’re looking into it. Please check back in a while because the article deserves it :)

  • grid7

    Hi Mitchell,

    “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)”

    Pagely.com (100% AWS backed) typically runs ES on the AWS node, or can connect to an ES cluster in the same AWS region for our customers which keeps latency pretty low.

    The table index concept is good hack. Nicely done.

  • Alex Brown

    Just read your entire article and I have to say it is awesome. I myself have a very large woo site with about 125,000 products on it which is ExtremeWheels.com

    My site loads ridiculously slow. Would you might looking at it and see if your solution in this article would help my site out?

    Much appreciated!
    -Alex

  • Great article, some things have been updated in ElasticPress that allow integration with WooCommerce, the results are awesome and wp-cli is no longer required. For Debian and Ubuntu users this tutorial will walk you through the entire process https://guides.wp-bullet.com/install-configure-elasticsearch-speed-woocommerce-search/

  • Brand Smith

    Very interesting article, thanks. Could this concept be adapted to improve product filter speed? We have a Woocommerce store with 20,000+ products and variations, and Ajax product filtering can take up to 6-8 seconds to return results.

    • Hi Brand! In short – I believe so. The index concept applies, but we haven’t looked into improving that part. Most of our customers have a large number of orders, rather than a large number of products.

      But, yea it should be possible, given that there’s a hook there that can somehow be used to trigger usage of an index table.

  • Radek

    Hi, I’m stuck at the point where I have to run the command: “wp wc_coi reset_index”. The error log keeps saying that specific table wp_options does not exist. No wonder since I use a custom database table. How to fix that? Thanks in advance.

  • Hi Mitchell,

    Adding indexes is cheap and fast to implement, probably the wisest workaround in the short term for small to midsize stores.

    And it’s true that the core of the performance problem is the join between post and metas tables. And it gets exponentially worse as you try to search in more metas fields.

    But I’m not sure that WooCommerce can solve that in the next releases with SQL, because the solution can come only, in my opinion, from denormalisation.
    Which is exactly the purpose of noSQL solutions like Elasticsearch: build a new dedicated data model for every search that require high performance. And therefore, the data model that suits backend orders search probably is not the same that suits front-end products search.

    For big stores, the long term solution is probably not SQL, especially when you add-up full text search, filters, spellchecking, highlighting.

  • Raymond Rutjes

    Hey, there, I thought I’d share a plugin I recently released.
    It enables you to index your orders into Algolia so that you can find them in milliseconds.
    The setup consists of creating an Algolia account and hit the index button.

    https://wordpress.org/plugins/wc-order-search-admin/

    PS: Works with WC 2 & 3

  • Tomason Sarin

    Thanks for this article Mitchell. Actually, we’re working with WooCommerce for a while now and optimized several of their queries in the last couple of years. For most of them, we were looking for the proper indexes manually (or in blogs like this one). Recently we used this sql optimizer which just removes that headache :) I subscribed to this blog, thanks for sharing this information.
    P.S, can you please share the specific SQL query that you optimized using the suggested index? Just want to check if it’s one that we already reviewed. Thanks!