Lesson 3.3. Catalog Queries

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 terms
  • return => 'ids' — when you don't need objects
  • Visibility and stock — the product_visibility taxonomy (outofstock, exclude-from-catalog)
  • meta_query on _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
<?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
<?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:

SQL
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

SymptomCauseFix
The catalog takes 5+ secondsposts_per_page => -1 and product objects in a loopPagination and return => 'ids'
The price filter is slowmeta_query on _priceThe lookup table or Product Collection block filters
The list is stale after a product changesA cache without invalidationClear the cache on woocommerce_update_product

Weak and strong answer

Question: How do you select 12 in-stock products under $20?

Weak answer. WP_Query with a meta_query on _price and _stock_status.

Strong answer. wc_get_products() with stock_status => instock and a price limit, or SQL against wc_product_meta_lookup, which has min_price and stock_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 with EXPLAIN.

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.

TEXT

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.

wp — Module 3. Product Catalog

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.

Save your progress

Sign in or create a free account to mark lessons as completed and continue the whole course. You will come back to this lesson.