WooCommerce core uses a post meta called _customer_user to store the customer ID for orders. Since the meta_value field of the wp_postmeta table is not indexed, this means that all the queries that need to filter orders by customer ID are very slow.
To address this, in PR #17895, we are exploring the idea of using post_author, instead of a post meta, to store the customer ID. _ customer_user will still be populated to preserve backward compatibility.
This change will significantly improve the performance of queries that need to filter orders by customer ID.
On a test database with about 2.5 million entries in the wp_posts table and about 100 million entries in the wp_postmeta table running on a local dev machine, the query used by WC core to filter orders by customers in admin dropped from 25 seconds when using _customer_user to 0.0008 seconds when using post_author. Those numbers were retrieved using the plugin query-monitor and the two different queries can be seen in the screenshots below.
Query using _customer_user:
Query using post_author:
Although WC core doesn’t use the post_author field for orders, before merging this change, we want to make sure that it won’t break any existing extensions that could be using post_author for something else. Hence this post to share our plan with the community and ask for feedback.
Please let us know of any concerns you might have in the comments below or directly in the PR. If there are no objections, we plan to ship this change in WooCommerce 3.4.
Leave a Reply