WooCommerce product archives and shop pages query the database on every page load to fetch products, prices, stock statuses, and attribute terms — a process that involves dozens of queries even for a modest catalogue of 200 products. Object caching with Redis or Memcached intercepts these repetitive queries at the $wpdb level, returning results from memory for all subsequent requests until a product is updated. Beyond object caching, query optimisation specific to WooCommerce involves understanding which queries are slow: product attribute filtering queries join wp_term_relationships, wp_term_taxonomy, and wp_postmeta in ways that benefit significantly from composite indexes. The WooCommerce product lookup table (wc_product_meta_lookup) was introduced to denormalise commonly filtered product meta — price, stock, rating — into a dedicated table with purpose-built indexes, bypassing the slow EAV pattern of wp_postmeta. Ensuring the lookup table is populated with WC_Product_Data_Store_CPT::get_data_for_lookup_table() or via WooCommerce → Status → Tools → Update product lookup table is the first step in diagnosing slow shop page queries. The WooCommerce REST API endpoint for products caches its responses poorly by default — adding a short transient layer around wc_get_products() calls in custom endpoints reduces repeated catalogue queries to a single database hit per cache TTL. The posts_clauses filter provides direct access to the SQL fragments WooCommerce generates for product archive queries, allowing targeted index hints or query modifications for specific filtering scenarios. Product image thumbnails served at incorrect sizes cause Cumulative Layout Shift — registering a dedicated image size with add_image_size() and referencing it in the loop template ensures consistent dimensions without client-side reflow. The WooCommerce asset dequeue post addresses the front-end payload side of shop performance — combine it with server-side query optimisation for the full improvement. The Transient API post explains the caching layer used in the product query wrapper below. Profile shop page queries with the Query Monitor plugin before and after changes — it lists every database query with timing data and the PHP call stack that triggered it.
Problem: WooCommerce shop archive pages execute dozens of unoptimised database queries per page load, causing slow Time to First Byte on catalogues larger than a few hundred products, especially when product filtering by attribute is enabled.
Solution: Enable the WooCommerce product lookup table, wrap wc_get_products() calls in a versioned transient cache, and use the posts_clauses filter to add STRAIGHT_JOIN hints that enforce the optimal join order for attribute-filtered product archive queries.
// 1. Regenerate product lookup table (run once via WP-CLI)
// wp wc tool run regenerate_product_lookup_table --user=1
// 2. Cached product query wrapper
function get_cached_products(array $args = [], int $ttl = HOUR_IN_SECONDS): array {
$version = (int) get_option('wc_products_cache_v', 1);
$cache_key = 'wc_products_' . md5(serialize($args)) . "_v{$version}";
$products = get_transient($cache_key);
if (false !== $products) return $products;
$query = new WC_Product_Query($args);
$products = $query->get_products();
set_transient($cache_key, $products, $ttl);
return $products;
}
// Invalidate cache on product save or stock change
add_action('woocommerce_update_product', function() {
update_option('wc_products_cache_v', (int) get_option('wc_products_cache_v', 1) + 1, false);
});
// 3. Optimise shop archive SQL join order for attribute filtering
add_filter('posts_clauses', function(array $clauses, WP_Query $q): array {
if (!$q->is_main_query() || !$q->get('wc_query')) return $clauses;
if (!str_contains($clauses['join'] ?? '', 'term_relationships')) return $clauses;
// Add STRAIGHT_JOIN to force the planner to follow the FROM table order
$clauses['fields'] = 'STRAIGHT_JOIN ' . ltrim($clauses['fields']);
return $clauses;
}, 10, 2);NOTE: The STRAIGHT_JOIN hint overrides the MySQL query planner's join order — verify with EXPLAIN on a representative attribute-filtered shop page query before and after applying it to confirm it actually reduces the estimated row count, as the optimal order varies by catalogue size and index cardinality.