Performance: Switching to post_author to store order customer IDs

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.


16 responses to “Performance: Switching to post_author to store order customer IDs”

  1. This is very exciting. 🤤 Nice work Rodrigo and co.!

  2. Anything which moves away from postmeta will be a Good Thing! Its often a place where we look to add indexes to speed things up (in the mean time).

  3. We have around one hundred thousand customers in our wordpress user table and every time i run query to filter users based on their order, server got timed out that’s because the user id saved in post meta key “_ customer_user” in the psotmeta table where the value field is not indexed. The above change could significantly help many others.

  4. In all the testing I’ve been doing with various plugins, I haven’t yet seen anything modifying the post_author field at all, so this is pretty rad!

  5. That would be great for admin and shop managers for sure! Great idea Rodrigo 😉

  6. Super cool to see this coming. Nice work @rodrigosprimo!

    The only issues I could see with it are:

    the historical use of user ID 1 on orders by default, which has already been addressed in the patch on the PR, including update code.
    Subscriptions’ use of post_author in the same way as Orders. If we update existing subscriptions to use 0 once 3.4 is active on the site, that won’t be an issue either. We’ll release code for that ASAP, but having this pushed back to 3.4 (instead of 3.3) and the new WC compatibility check will help ensure folks update Subscriptions to a version that includes that code before updating WooCommerce to 3.4. ❤️

  7. What about the security. As I know authors always have permission to the posts which are saved with their user ID. Also don’t forget about the restAPI. This could lead into security issues.

    I like the performance improvements but not the way, how it was handled. The post_author field is thought for the owner (shop manager), not for the associated user (customer).

    1. Customers don’t have edit_post capability. Regardless of being author they cannot access afaik.

      1. Yeah, but what if authors or Moderators buy something? Normally then they get acccess to the order by restAPI or admin panel..

        1. Then thats something we can lock down, if it really is the case. Doubtful because our custom post type has it’s own set of capabilities. Orders do not use the same caps as posts.

  8. That’s great news – I’ve been having huge performance issues because of the user ID being stored as post meta and this change will significantly improve all of my user based order queries. I just hope that the database records will be also updated for past orders after installing the update, so we can take advantage of it right away.

    1. Yes, past orders will be updated.

  9. Sounds like a great idea.

    Could this also sort out the slight weirdness in terms of ‘guests’ and ‘customers’ being seperate – surely every one is a customer – but some just haven’t setup an account.

    Would be good if ‘guest’ checkouts could still be ‘customers’ and WooCommerce would just create a user record for them – then you could see order history for guest accounts.

    This should also help with working out metrics like customer lifetime value.

  10. Jesper Nielsen Avatar
    Jesper Nielsen

    Overall i would be happy about the change.

    I have some thougts.
    Since the link between the customer and the order will be more “visible” i see some issues regarding people using the users emails and data, instead of the order email and data.

    What happens when a user changes some info, for example an address or email. Will the change take place on the order too? – That could be nice, but also a grey area in case of products alreay been sent to the customer, where the address is wrong. Then the shop has no prove of the data beeing wrong in the first place, if the customer has edited the address after shipment.

    i dont know if the change would effect the scenario. i was just wondering.

  11. Tobin Fekkes Avatar
    Tobin Fekkes

    wp_delete_user() deletes all posts where post_author = $user_id ….
    https://core.trac.wordpress.org/browser/tags/4.9.2/src/wp-admin/includes/user.php#L305

    …. if $reassign is not set, and the post type declaration of $post_type->delete_with_user is true
    https://core.trac.wordpress.org/browser/tags/4.9.2/src/wp-admin/includes/user.php#L339

    So if, for any reason (can’t think of why yet, but it’s possible), someone were to use add_filter(‘woocommerce_register_post_type_shop_order’) to set $post_type->delete_with_user to TRUE, all their orders would be deleted when the user is deleted.

    Definitely an edge case, but certainly possible, and would really mess up reporting or historical sales data. Just something to be aware of in case someone is trying to track down a bunch of lost sales in the future.

    Despite that, LOVE the proposal of moving to post_author. These “_customer_user” queries are killing me!

  12. ryansmiller Avatar
    ryansmiller

    This is a great proposal and an A+ performance upgrade. Thanks for taking the time to review the queries in WC and optimizing them.

Leave a Reply

Your email address will not be published. Required fields are marked *