Monday, 8:30 AM
The catalog has grown to 30,000 products, and a product page takes 6 seconds.
Query Monitor shows why: the "Related products" block runs posts_per_page => -1 with a meta_query, then builds an object for every product to pick four.
At 200 products nobody noticed.
Goal
After the lesson you write catalog queries that handle tens of thousands of products: the right wc_get_products() arguments, pagination, a cache with invalidation, and checks in Query Monitor and EXPLAIN.
Key concepts
wc_get_products()/WC_Product_Query— queries in WooCommerce termsreturn => 'ids'— when you don't need objects- Visibility and stock — the
product_visibilitytaxonomy (outofstock,exclude-from-catalog) meta_queryon_price— slow; the lookup table — fast- Cache the result: a transient or object cache, invalidated on product save
EXPLAIN— check which indexes a query uses
Diagram
flowchart LR
Q["In-stock products<br/>under $20"] --> W{"How to query?"}
W -- "WP_Query + meta_query _price" --> SLOW["JOIN wp_postmeta × N<br/>slow"]
W -- "wc_get_products()" --> FAST["lookup table + taxonomies<br/>fast"]
FAST --> C["cached result<br/>+ invalidation"]
Code
A query with wc_get_products():
<?php
$ids = wc_get_products( [
'status' => 'publish',
'stock_status' => 'instock',
'visibility' => 'catalog',
'category' => [ 'hoodies' ],
'limit' => 12,
'page' => 1,
'orderby' => 'date',
'order' => 'DESC',
'return' => 'ids',
] );
A cache with invalidation:
<?php
function my_sale_product_ids(): array {
$ids = get_transient( 'my_sale_ids' );
if ( false === $ids ) {
$ids = wc_get_product_ids_on_sale(); // WooCommerce caches this too
set_transient( 'my_sale_ids', $ids, HOUR_IN_SECONDS );
}
return $ids;
}
add_action( 'woocommerce_update_product', static fn () => delete_transient( 'my_sale_ids' ) );
A price range from the lookup table:
SELECT product_id, min_price, stock_status
FROM wp_wc_product_meta_lookup
WHERE min_price <= 20 AND stock_status = 'instock'
ORDER BY min_price
LIMIT 12;
Common mistakes
| Symptom | Cause | Fix |
|---|---|---|
| The catalog takes 5+ seconds | posts_per_page => -1 and product objects in a loop | Pagination and return => 'ids' |
| The price filter is slow | meta_query on _price | The lookup table or Product Collection block filters |
| The list is stale after a product changes | A cache without invalidation | Clear the cache on woocommerce_update_product |
Weak and strong answer
Question: How do you select 12 in-stock products under $20?
Weak answer.
WP_Querywith ameta_queryon_priceand_stock_status.
Strong answer.
wc_get_products()withstock_status => instockand a price limit, or SQL againstwc_product_meta_lookup, which hasmin_priceandstock_status. I take IDs only, paginate, cache the result and clear the cache when a product changes. I check the query in Query Monitor and withEXPLAIN.
Why the second is stronger: The candidate knows where WooCommerce keeps fast data and thinks about caching.
Practice
Two separate cases: first fix the code, then see why it's faster.
Case 1. Related products without the full scan (IDE)
Rewrite the "Related products" function from the start of the lesson: 4 in-stock products from the same category, excluding the current one, cached and invalidated on product save.
Case 2. Compare the queries (terminal)
Run EXPLAIN on a wp_postmeta price query and on the lookup table. Compare type and rows. On your own site, measure the query count in Query Monitor before and after caching.
Interview Questions
Not in principle: wc_get_products() is a wrapper that creates a WC_Product_Query and returns the result.
With the outofstock term in the product_visibility taxonomy and the "Hide out of stock items" option. Catalog queries add a tax_query on that taxonomy.
wc_get_product_id_by_sku( $sku ) — a lookup table query. Not a meta_query on _sku.