How We Sped Up Craft Commerce 5 By Removing Catalog Pricing

Craft Commerce 5 introduced a powerful new catalog pricing system that supports flexible, store-specific price adjustments. But if you're not using it, you might still be paying the performance cost.

We recently experienced this performance hit on a project with a large catalog when we upgraded to Craft 5 and Commerce 5.

🐌 The Problem: Hidden Overhead in Every Query #

By default, Commerce 5 adds a JOIN and subquery for catalog pricing to all product and variant queries, whether you're using it or not. That means:

• Slower page loads on both the front-end and in the control panel
• Unnecessary complexity in your SQL queries
• Performance degradation as your catalog grows

For a site with a large catalog and no need for catalog-specific pricing logic, this overhead is wasteful and avoidable.

🛠️ Our Workaround: Skip What You Don’t Need #

To solve this, we patched the query for both ProductQuery and VariantQuery using Craft’s event system. Our goals were simple:

• Remove the catalog pricing join and subquery
• Replace any reference to catalogprices.price with the default price column:
  • commerce_products.defaultPrice for products
  • purchasables_stores.basePrice for variants
• Add dummy columns (promotionalPrice, salePrice) as NULL to satisfy any expected aliases and avoid SQL errors

This immediately improved performance on product listing pages and simplified the generated SQL.

✨ The Code #

Here’s a simplified version of the patch you can adapt for your site:

use craft\commerce\elements\db\ProductQuery;
use craft\commerce\elements\db\VariantQuery;
use yii\base\Event;

Event::on(
    ProductQuery::class,
    ProductQuery::EVENT_BEFORE_PREPARE,
    function(Event $event) {
        $query = $event->sender;

        // Remove catalog pricing join
        $query->query->join = array_filter(
            $query->query->join, 
            function($join) {
                return strpos(
                    $join[1], 
                    'catalogprices'
                ) === false;
            }
        );

        // Replace price reference
        $query->query->select = array_map(
            function($column) {
                return str_replace(
                    'catalogprices.price',
                    'commerce_products.defaultPrice', 
                    $column
                );
        }, $query->query->select ?? []);

        // Add expected columns
        $query->query->addSelect([
            'NULL AS promotionalPrice',
            'NULL AS salePrice',
        ]);
    }
);

Event::on(
    VariantQuery::class,
    VariantQuery::EVENT_BEFORE_PREPARE,
    function(Event $event) {
        $query = $event->sender;

        // Remove catalog pricing join
        $query->query->join = array_filter(
            $query->query->join, 
            function($join) {
                return strpos(
                    $join[1],
                    'catalogprices'
                ) === false;
            }
        );

        // Replace price reference
        $query->query->select = array_map(
            function($column) {
                return str_replace(
                    'catalogprices.price',
                    'purchasables_stores.basePrice',
                    $column
                );
            },
            $query->query->select ?? []
        );

        // Add expected columns
        $query->query->addSelect([
            'NULL AS promotionalPrice',
            'NULL AS salePrice',
        ]);
    }
);

✅ Why It’s Safe – and Worth It #

If you’re not using catalog pricing, these joins and subqueries serve no purpose. This patch ensures that any expected aliases are still present (just as NULL), so your templates and control panel don’t break.

By skipping the catalog pricing logic, we saw significantly faster queries and reduced SQL complexity-without sacrificing functionality.

🧠 Why It Matters for Large Catalogs #

For the average site with a few hundred or even a few thousand products, the impact of an unused join and subquery may be negligible. MySQL or Postgres can usually optimize small queries quickly, even when joins aren’t strictly necessary.

But for large-scale catalogs (e.g., hundreds of thousands of products or variants), every added join and subquery multiplies the cost:

• 🐢 Index scans and table joins take longer
• 🧮 Query preparation must account for more conditions
• 📉 Caching becomes less effective

And when these queries run frequently on collection pages, dashboards and filters–the performance hit grows dramatically.

By removing the joins you don’t need, you reduce:

• 📊 The number of rows scanned and joined
• 🧩 The complexity of the query
• 🫠 The strain on CPU, memory, and database cache

So while the average small shop might never notice, large enterprise catalogs absolutely will.

🔍 Final Thoughts #

Craft Commerce is a powerful platform, and it’s designed to be flexible. But with that flexibility can come some hidden costs. Especially if features are included by default.

This small patch is a great example of how Craft’s event-driven architecture makes it easy to fine-tune performance based on your site’s actual needs.

Let us know if you’ve seen similar wins—or if you’re looking for help optimizing your Craft Commerce store.