Optimize counting of orders pending synchronization

[hwong-godaddy]

Describe the solution you'd like

On sites with ALOT of data/orders going back as far as 2015 -- the following derived query causes the DB to spike and causing WP to hang

---

## Currently derived query is below 
## -- takes approx. 9.42 seconds 
SELECT( 
        ( 
SELECT COUNT(1) FROM wp_posts posts 
LEFT JOIN wp_wc_orders orders ON posts.id=orders.id 
WHERE 
  posts.post_type in ('shop_order', 'shop_order_refund', 'shop_subscription') 
  AND posts.post_status != 'auto-draft' 
  AND orders.id IS NULL) 
        + 
        (SELECT COUNT(1) FROM ( 
                SELECT orders.id FROM wp_wc_orders orders 
                JOIN wp_posts posts on posts.ID = orders.id 
                WHERE 
                  posts.post_type IN ('shop_order', 'shop_order_refund', 'shop_subscription') 
                  AND orders.date_updated_gmt < posts.post_modified_gmt 
                  ) x) 
) count 

## Recommended query to only include orders newer than 730 days 
## -- takes approx. 1.59 seconds 
SELECT( 
        ( 
SELECT COUNT(1) FROM wp_posts posts 
LEFT JOIN wp_wc_orders orders ON posts.id=orders.id 
WHERE 
  posts.post_type in ('shop_order', 'shop_order_refund', 'shop_subscription') 
  AND posts.post_status != 'auto-draft' 
  AND orders.id IS NULL 
  AND (orders.date_updated_gmt > DATE_SUB(NOW(), INTERVAL 730 DAY)) 
  ) 
        + 
        (SELECT COUNT(1) FROM ( 
                SELECT orders.id FROM wp_wc_orders orders 
                JOIN wp_posts posts on posts.ID = orders.id 
                WHERE 
                  posts.post_type IN ('shop_order', 'shop_order_refund', 'shop_subscription') 
                  AND orders.date_updated_gmt < posts.post_modified_gmt 
                  AND (orders.date_updated_gmt > DATE_SUB(NOW(), INTERVAL 730 DAY)) 
                  ) x) 
) count 

## Recommended query to only include orders newer than 365 days 
## -- takes approx. .74 second 
SELECT( 
        ( 
SELECT COUNT(1) FROM wp_posts posts 
LEFT JOIN wp_wc_orders orders ON posts.id=orders.id 
WHERE 
  posts.post_type in ('shop_order', 'shop_order_refund', 'shop_subscription') 
  AND posts.post_status != 'auto-draft' 
  AND orders.id IS NULL 
  AND (orders.date_updated_gmt > DATE_SUB(NOW(), INTERVAL 365 DAY)) 
  ) 
        + 
        (SELECT COUNT(1) FROM ( 
                SELECT orders.id FROM wp_wc_orders orders 
                JOIN wp_posts posts on posts.ID = orders.id 
                WHERE 
                  posts.post_type IN ('shop_order', 'shop_order_refund', 'shop_subscription') 
                  AND orders.date_updated_gmt < posts.post_modified_gmt 
                  AND (orders.date_updated_gmt > DATE_SUB(NOW(), INTERVAL 365 DAY)) 
                  ) x) 
) count

Describe alternatives you've considered

I'm not sure what the best way would be to allow users to

• Default to a full lookback for DataSynchronizer.php
• Limit lookback to only orders within two years

Another possibility would be to mark orders with a flag - need_sync and pass that to DataSynchronizer.php to only process orders that need_sync.

Additional context

No response

[beaulebens]

We've opened a discussion about how to handle this over here: https://github.com/woocommerce/woocommerce/discussions/40757

[collette-tamez]

Any chance this issue is still on the Woo team's radar?