A fintech platform runs daily risk scoring across 50 million transactions. The risk team's WHERE clause filters transactions flagged in the last 7 days, merchant category = fuel, amount > $500, country NOT IN approved list. This must execute in under 200 ms. A missing composite index on (flagged_at, merchant_category, amount) turns this into a 90-second full scan. WHERE clause design is the difference between a real-time dashboard and a batch report.

E-Commerce: High-Value Order Dashboard

A business intelligence team needs a real-time dashboard showing high-value orders from active customers in specific regions over the past 30 days. The query must run in under 100 ms on a 50M-row orders table.

Composite index design ensures the query touches fewer than 10,000 rows out of 50M. NOT EXISTS correctly excludes flagged orders even when fraud_flags.order_id has NULLs. Dashboard query time drops from 12 seconds to 45 ms.

Optimized multi-predicate WHERE with composite index strategy

-- Index required: (status, region, created_at) on orders

-- Index required: (tier, is_active) on customers

SELECT

o.id,

o.total,

o.created_at,

c.name AS customer_name,

o.region

FROM orders o

JOIN customers c ON c.id = o.customer_id

WHERE o.status = 'completed'

AND o.region IN ('NA', 'EMEA')

AND o.created_at >= NOW() - INTERVAL 30 DAY

AND o.total >= 5000

AND c.tier = 'enterprise'

AND c.is_active = 1

ORDER BY o.total DESC

LIMIT 100;

Anti-join: orders with no associated fraud flag

SELECT o.id, o.total, o.customer_id

FROM orders o

WHERE o.status = 'completed'

AND o.total >= 5000

AND NOT EXISTS (

SELECT 1

FROM fraud_flags ff

WHERE ff.order_id = o.id

AND ff.resolved = 0

)

ORDER BY o.total DESC

LIMIT 50;

All

SaaS Platform: Tenant-Scoped Event Queries

A multi-tenant analytics platform must enforce tenant isolation in every query. WHERE tenant_id = ? is the mandatory first predicate — it is the leading column in all composite indexes. Any query missing this predicate causes a cross-tenant full scan.

Mandatory tenant_id as the leading WHERE predicate and first index column guarantees tenant isolation and enables index seeks. Funnel drop-off analysis using NOT EXISTS correctly handles users who started near the window boundary.