Woocommerce: High performance queries with thousands of orders

Hi!

Trying to avoid TL;DR :slight_smile:

  • We sell physical products and tickets.
  • A ticket is “simply” a product that has a meta in order_item: is_ticket=1

Everything is ok until the real scenario:

  • 150.000 ~ 160.000 orders/year.
  • Many orders has more than 1 line item, may be 5…
  • Some are products, 95% are tickets
  • Some tickets has another meta: ‘is_invitation=1’

Question:
How to perform thouse high performance queries using woocommerce classes and helpers, in a milisecond, like we do in SQL directly.
(We don’t want to lose compatibility, hardcoding SQL)

Basically, count total quantity sold of…

  • a given product_id
  • total invitations
    and similar to those…

Best regards!!!

Hey @josepon,

Welcome to the community :tada:

Are you seeing any errors when trying to do it in a workflow? If it was me just because it is a lot of data rather than using the WooCommerce node which uses the API I would use a database node and query the information that way it will result in a quicker workflow and you won’t have to worry about memory issues.

Hi!

the point is where the data is processed: PHP or SQL Engine.
If the counting are done in PHP, will be inefficient.
We need that helpers prepare the selects in the form of “SELECT COUNT()” to delegate the counting to SQL Engine.