From 23 seconds to 2: killing a 32-join view query

In one line

A support page everyone used took 23 seconds to load. I traced it to a clean-named database view hiding 32 joins across ~3.9M rows - run twice per request just to get a list of IDs - and brought the page to ~2 seconds with smarter querying and a one-line cache. No new infrastructure.

The problem

A support agent reported that the customer store-listing page was painfully slow. Locally it took 23.3 seconds - and this is a page support staff open dozens of times a day, on a platform handling real-time payment operations. "Slow" here is a queue of agents waiting on a spinner.

The endpoint was a paginated list of a customer's stores, filtered by the logged-in user's access restrictions. Two things about that sentence turned out to matter: paginated (so it runs two queries - data + count) and filtered by access restrictions (where the real cost was hiding).

Where the time went

Both the data query and the count query passed through the same access-control filter, which layered two checks. One was cheap. The other called a method that asked a deceptively simple question: "which customer IDs are visible to this user, given their provider permissions?"

The query behind it looked harmless:

SELECT DISTINCT pm.retailerId
FROM view_product_details_retailer_mapping pm
WHERE 1 = 1;  -- user has global rights → no extra filter

But view_product_details_retailer_mapping was not a table. It was a view built on top of another view - view_product_details - which itself was 32 LEFT JOINs: products joined to providers, two currencies, countries, product type / line / brand / category / subcategory, validity, voucher and PIN types, ten attribute tables, assignment groups, divisions, customers… To answer "give me a list of distinct IDs," the database was materializing 32 joins across 3.9 million rows - and, because the page is paginated, doing it twice per request.

Investigation: EXPLAIN told the whole story

The fix started with proving where the cost was. EXPLAIN on the two ways of getting the same list of IDs:

Same 2,848 IDs. Same result. ~660× difference - because the base-table query is fully answered by a composite index and never touches row data, while the view forces a full scan and a temporary table for the DISTINCT. Run that twice per request, add the actual store queries and network overhead, and you land on 23 seconds.

(A small but real detail: I was inspecting the view through a custom MCP database server that let me query via the app's Doctrine connection - and found its output was truncating SHOW CREATE VIEW at 80 characters, hiding the very joins I needed to see. Fixing that truncation is what unblocked the investigation. Tooling that lies to you costs more than slow tooling.)

The fix: branch by what the caller actually needs

The expensive query treated every user the same. But the restriction type determines how much work is genuinely required, so I split it into three paths:

Path 1 - global rights (the common case): drop to DBAL on the base table. Most users can see all providers, so the join was pure waste. For them, skip Doctrine's DQL and the view entirely and hit the indexed base table directly:

if ($this->providerRestrictions->hasGlobalRights()) {
    $rows = $this->connection
        ->executeQuery('SELECT DISTINCT id_customer FROM product_retailer_mapping')
        ->fetchFirstColumn();

    return $this->cachedIds = array_map(intval(...), $rows);
}

Path 2 - no rights: return nothing, for free.

if (!$this->providerRestrictions->hasRights()) {
    return $this->cachedIds = [];
}

Path 3 - specific restrictions (rare): keep the view. Only the genuinely restricted users fall back to the original DQL-on-view query - the one case that actually needs the joined data.

Then: a per-request cache, for free. Because the page runs both a data and a count query, the lookup happened twice. The repository instance is shared within a request by the DI container, so memoizing on a private property eliminates the second call with zero infrastructure:

if ($this->cachedIds !== null) {
    return $this->cachedIds; // second caller (the count query) pays nothing
}

The result

No new dependencies. No schema changes. No Redis, no hardware. Just not doing 32 joins when you only need a list of IDs.

Why this is an architecture story, not just a SQL story

• The legacy system had the exact same query. It was simply masked by fewer concurrent users and different caching. When you migrate a feature, replicating the old behavior faithfully also replicates its mistakes - so migration has to mean questioning the old design, not just porting it. That mindset is built into how I run the whole re-platforming.
• Views hide complexity behind a friendly name. view_product_details_… reads as harmless in a query. Always check what a view actually expands to before you build on it.
• The ORM doesn't know about your indexes. DQL is the right default; when a hot path needs an index the ORM won't reach, drop to DBAL for that path and keep DQL for the complex-but-rare cases.
• The cheapest cache is the one you don't have to operate. A private ?array killed half the cost before any infrastructure was involved.

Performance work isn't always about adding caches or scaling hardware. Often it's about removing work that never needed to happen.