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