Query Optimization

Query optimization in Twig-heavy storefronts is about reducing unnecessary database work and stabilizing response times under traffic. This guide documents the baseline rules you should apply on all listing and detail pages.

Core Rules

1. Fetch only required columns.
2. Eager load relations you render.
3. Use limits for any list query.
4. Avoid duplicate queries in nested loops.
5. Cache expensive, read-heavy query results.

Eager Loading to Prevent N+1

{% set entries = craft.entries
    .section('news')
    .with(['author', 'category'])
    .limit(10)
    .all() %}

Without eager loading, each relation access can trigger extra queries per result row.

Narrow Select Scope

{% set entries = craft.entries
    .section('news')
    .select(['title', 'summary', 'postDate'])
    .limit(10)
    .all() %}

Avoid over-fetching large fields you do not render.

Count/Existence Optimization

Use specialized query methods when full records are not needed.

{% set total = craft.entries.section('news').count() %}

Prefer count(), exists(), and targeted lookups over loading full element sets.

Listing Query Pattern for Yui Products

{% set products = craft.yuiProducts(
    ['id', 'title', 'uri', 'yuiPrice'],
    { enabled: true },
    {'id': 3},
    16,
    ['productMediaAsset']
).cache(900).all() %}

This pattern keeps listings predictable and avoids hidden query explosion.

Query Review Checklist

Before shipping:

1. verify relation-heavy templates use eager loading
2. verify listing limits are set
3. verify no duplicate query calls inside loops
4. verify expensive queries are cached with correct TTL
5. profile query count in debug toolbar for key pages

Common Anti-Patterns

• calling .all() early and filtering in Twig
• querying inside loop bodies
• missing limits on listing endpoints
• caching dynamic customer/cart datasets globally