Customer API Endpoint not scalable

[MillerMedia]

Prerequisites

• I have searched for similar issues in both open and closed tickets and cannot find a duplicate
• The issue still exists against the latest master branch of WooCommerce on Github (this is not the same version as on WordPress.org!)
• I have attempted to find the simplest possible steps to reproduce the issue
• I have included a failing test as a pull request (Optional)

Steps to reproduce the issue

1. Make a request to either the 'Retrieve a customer' (http://woocommerce.github.io/woocommerce-rest-api-docs/#retrieve-a-customer) or the 'List all customers' endpoints (http://woocommerce.github.io/woocommerce-rest-api-docs/#list-all-customers) on a store with a high amount of orders and customers (400,000+ orders/20+ million meta rows; 200,000+ customers)

Expected/actual behavior

When I follow those steps, I see...

The expected API results but with a much higher than expected performance hit on the database.

I was expecting to see...

A much quicker response with less of an effect on the database. We are running an export of customers to a ERP system to sync data between Woo and the ERP. We are on a site with 20 million+ post meta rows and tons of users and user meta. Between all of the table joins on the user and posts tables, it tied our database up pretty quickly. It would appear that these fields are the problem:

is_paying_customer | bool | Is the customer a paying customer? READ-ONLY
orders_count | integer | Quantity of orders made by the customer. READ-ONLY
total_spent | string | Total amount spent. READ-ONLY

These three fields have to access the post and post meta tables.

We wrote a temporary API endpoint extension that grabs only customer data (with relevant order data) and it worked much more quickly/efficiently. A potential fix would be to either make those three fields optional (that can be explicitly requested when making the call) or create the ability to exclude those from the query if they are not needed.

I'm not sure what size of site WooCommerce generally considers within the 'support' range (this may be outside of that) but either way it may be a good option since it's a relatively small fix for a potentially major scaling issue.

Isolating the problem

• This bug happens with only WooCommerce plugin active
• This bug happens with a default WordPress theme active, or Storefront
• I can reproduce this bug consistently using the steps above

WordPress Environment

` ### WordPress Environment ###

Home URL: https://.com
Site URL: https://.com
WC Version: 3.1.1
Log Directory Writable: ✔
WP Version: 4.8.2
WP Multisite: ✔
WP Memory Limit: 512 MB
WP Debug Mode: –
WP Cron: ✔
Language: en_US

Server Environment

Server Info: Apache/2.4.6 (CentOS) OpenSSL/1.0.2k-fips mod_wsgi/3.4 Python/2.7.5
PHP Version: 5.6.31
PHP Post Max Size: 8 MB
PHP Time Limit: 300
PHP Max Input Vars: 1000
cURL Version: 7.29.0
NSS/3.28.4

SUHOSIN Installed: –
Max Upload Size: 8 MB
Default Timezone is UTC: ✔
fsockopen/cURL: ✔
SoapClient: ✔
DOMDocument: ✔
GZip: ✔
Multibyte String: ✔
Remote Post: ✔
Remote Get: ✔

Database

WC Database Version: 3.1.1
WC Database Prefix: wp_2_
woocommerce_sessions: ✔
woocommerce_api_keys: ✔
woocommerce_attribute_taxonomies: ✔
woocommerce_downloadable_product_permissions: ✔
woocommerce_order_items: ✔
woocommerce_order_itemmeta: ✔
woocommerce_tax_rates: ✔
woocommerce_tax_rate_locations: ✔
woocommerce_shipping_zones: ✔
woocommerce_shipping_zone_locations: ✔
woocommerce_shipping_zone_methods: ✔
woocommerce_payment_tokens: ✔
woocommerce_payment_tokenmeta: ✔

Security

Secure connection (HTTPS): ✔
Hide errors from visitors: ✔

Active Plugins (54)

AffiliateWP - PayPal Payouts: by Pippin Williamson and Andrew Munro – 1.1.7
AffiliateWP: by AffiliateWP
LLC – 2.1-beta1

AffiliateWP - Affiliate Forms For Ninja Forms: by Pippin Williamson and Andrew Munro – 1.1.4
AffiliateWP - Allowed Products: by Pippin Williamson and Andrew Munro – 1.0.2
AffiliateWP - Store Credit: by AffiliateWP Team – 2.1.2
Bootstrap 3 Shortcodes: by Michael W. Delaney
Filip Stefansson
and Simon Yeldon – 3.3.10

Cloudflare: by John Wineman
Furkan Yilmaz
Junade Ali (Cloudflare Team) – 3.3.1

Coaching Site Custom Actions (THM): by Max Strukov ( Miller Media ) – 1.1
Contact Form 7: by Takayuki Miyoshi – 4.4.2
Username Editor: by Michael Robinson (Miller Media) – 0.1.0
ElasticPress: by Taylor Lovett
Matt Gross
Aaron Holbrook
10up – 2.3.2

Homepage Control: by WooThemes – 2.0.1
Jetpack by WordPress.com: by Automattic – 4.4.2
Cart Import: by LitExtension – 1.0.0
Maintenance Mode: by Lukas Juhas – 2.3.1
Prevent Duplicate Orders: by Michael Robinson – 1.0.0
Cal's Order Report (THM): by Max Strukov ( Miller Media ) – 1.1.1
Special Store Pricing for Members: by Michael Robinson – 1.6.7
Nav Menu Roles: by Kathy Darling – 1.8.6
Ninja Forms: by The WP Ninjas – 3.0.20
Product Sales Report for WooCommerce: by Potent Plugins – 1.4.6
Redirection: by John Godley – 2.7.1
Simple Responsive Slider: by marcelotorres – 0.2.2.5
Storefront Site Logo: by Wooassist – 1.2.1
Storefront WooCommerce Customiser: by WooThemes – 1.8.2 – 1.9.2 is available
User Role Editor: by Vladimir Garagulya – 4.35.3
WooCommerce AvaTax: by SkyVerge – 1.5.0-dev.1
WooCommerce Cost of Goods: by SkyVerge – 2.5.0
WooCommerce Customer/Order CSV Export: by WooThemes / SkyVerge – 4.0.7 – 4.3.7 is available
WooCommerce Email Customizer: by cxThemes – 3.12
WooCommerce Email Test: by RaiserWeb – 1.8
WooCommerce Authorize.Net CIM Gateway: by SkyVerge – 2.7.0 – 2.7.1 is available
WooCommerce Memberships: by SkyVerge – 1.8.9 – 1.9.0 is available
WooCommerce MSRP Pricing: by Lee Willis – 2.9.4
WooCommerce Order Status Control: by SkyVerge – 1.8.0
WooCommerce Product Bundles: by SomewhereWarm – 5.4.2 – 5.4.3 is available
Product Sales By State and Timeframe: by Michael Robinson
Miller Media – 1.0

WooCommerce Shipment Tracking: by WooCommerce – 1.6.4 – 1.6.8 is available
WooCommerce Shipping Dashboard: by Michael Robinson
Miller Media – 1.3.1

WooCommerce FedEx Shipping: by WooCommerce – 3.4.9
WooCommerce USPS Shipping Explicit Labels: by Miller Media (Michael Robinson) – 1.0
WooCommerce USPS Shipping: by WooCommerce – 4.4.9 – 4.4.10 is available
WooCommerce - ShipStation Integration: by WooCommerce – 4.1.18
WooCommerce SKUVault Connection: by Miller Media (Michael Robinson) – 1.6
WooCommerce - Store Toolkit: by Visser Labs – 1.5.9
WooCommerce Warehouse Router: by Michael Robinson
Miller Media – 1.0.0

WooCommerce: by Automattic – 3.1.1 – 3.1.2 is available
wpMandrill: by Mandrill – 1.33
Amazon Web Services: by Delicious Brains – 1.0.3
User Role Editor: by Vladimir Garagulya – 4.35.3
WP Offload S3 Lite: by Delicious Brains – 1.2
Cloudflare: by John Wineman
Furkan Yilmaz
Junade Ali (Cloudflare Team) – 3.3.1

Clicky for WordPress: by Team Yoast – 1.6
Coaching Site Custom Actions (THM): by Max Strukov ( Miller Media ) – 1.1

Settings

API Enabled: ✔
Force SSL: ✔
Currency: USD ($)
Currency Position: left
Thousand Separator: ,
Decimal Separator: .
Number of Decimals: 2
Taxonomies: Product Types: bundle (bundle)
external (external)
grouped (grouped)
simple (simple)
variable (variable)

Taxonomies: Product Visibility: exclude-from-catalog (exclude-from-catalog)
exclude-from-search (exclude-from-search)
featured (featured)
outofstock (outofstock)
rated-1 (rated-1)
rated-1 (rated-1-2)
rated-2 (rated-2)
rated-3 (rated-3)
rated-4 (rated-4)
rated-5 (rated-5)

WC Pages

Shop base: #87 - /
Cart: #81 - /cart/
Checkout: #83 - /checkout/
My account: #5829 - /my-account/

Theme

Name: Storefront Child
Version: 1.0.0
Author URL: http://example.com
Child Theme: ✔
Parent Theme Name: Storefront
Parent Theme Version: 2.2.5
Parent Theme Author URL: https://woocommerce.com/
WooCommerce Support: ✔

Templates

Overrides: storefront-child/woocommerce/archive-product.php
storefront-child/woocommerce/content-single-product.php
storefront-child/woocommerce/product-searchform.php
storefront-child/woocommerce/single-product.php

Authorize.Net CIM

Environment: Production
Tokenization Enabled:
Debug Mode: Off

Customer/Order CSV Export

Order Export Format: default_one_row_per_item
Customer Export Format: default
Automatically Export Orders: disabled
Automatically Export Customers: disabled
`

[claudiosanches]

We wrote a temporary API endpoint extension that grabs only customer data (with relevant order data) and it worked much more quickly/efficiently. A potential fix would be to either make those three fields optional (that can be explicitly requested when making the call) or create the ability to exclude those from the query if they are not needed.

Yep, I want to remove those fields too.

Moving it to your REST API repository where we are working on REST API v3: woocommerce/wc-api-dev#53

Thanks for your contribution.