# Aem Query Optimization

> AEM Query Optimization

- Skill: `gabrielmoreira/aem-query-optimization` (Agent Skill)
- Install (CLI): `npx skillmds@latest add gabrielmoreira/aem-query-optimization`
- Raw SKILL.md: https://api.skillmd.com/api/skills/gabrielmoreira/aem-query-optimization/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: gabrielmoreira (https://skillmd.com/u/gabrielmoreira)
- Updated: 2026-09-21
- Page: https://skillmd.com/skills/gabrielmoreira/aem-query-optimization

---

# AEM Query Optimization

## Purpose
Write, analyze, and optimize JCR queries (SQL2, XPath, QueryBuilder) for maximum performance including proper index utilization, result set management, and query pattern best practices.

## When to Use (Triggers)
- User mentions "query," "QueryBuilder," "SQL2," "XPath," or "search"
- References to slow queries, traversal warnings, or query explain plans
- Questions about search predicates, result pagination, or faceted search
- Requests involving content queries for components, servlets, or workflows
- Discussion of query performance, index usage, or query limits

## Core Capabilities
- Write optimized queries using QueryBuilder API, JCR-SQL2, and XPath
- Analyze query explain plans and optimize for index utilization
- Implement efficient pagination, sorting, and result limiting
- Design search experiences with facets, suggestions, and fulltext
- Convert between query languages and select the most efficient approach

## Domain Knowledge Required
### Technical Foundation
- JCR query languages: SQL2, XPath, and their capabilities
- Query optimizer behavior: cost estimation, index selection, traversal
- Pagination patterns: offset vs. keyset, cursor-based pagination
- Full-text search concepts: analyzers, stemming, boost, relevance

### AEM-Specific Context
- QueryBuilder API predicates and parameter syntax
- QueryBuilder PredicateEvaluator extension mechanism
- AEM Query Debugger tool (query explain, timing)
- Oak query engine traversal limits and configuration
- Index cost estimation and query plan selection
- Common OOTB indexes and their covered query patterns

## Implementation Approach
### Step 1: Query Requirements
Define what data needs to be retrieved and how.
- Identify the content type and properties to query
- Determine filtering conditions (path, type, property values, date ranges)
- Define sorting requirements and sort index availability
- Establish result size expectations and pagination needs

### Step 2: Query Writing
Craft the query using the appropriate language.
- Use QueryBuilder for dynamic, programmatic query construction
- Use JCR-SQL2 for complex joins or subqueries
- Apply path restrictions to narrow query scope
- Include nodetype restrictions for Oak index optimization
- Limit results explicitly (p.limit in QueryBuilder)

### Step 3: Explain Plan Analysis
Validate query uses indexes effectively.
- Run query through AEM Query Debugger (Tools → Query Performance)
- Check explain plan for "traverse" vs. "index" execution
- Verify estimated cost and actual node traversal count
- Identify missing indexes for covered properties

### Step 4: Performance Optimization
Apply optimization techniques to problematic queries.
- Add path restrictions to reduce search scope
- Ensure property used in WHERE clause exists in an index
- Use native full-text constraints for text search (not LIKE '%x%')
- Implement keyset pagination instead of offset for large result sets
- Cache query results when data freshness allows

### Step 5: Production Safeguards
Prevent queries from impacting system performance.
- Set query traversal limits appropriate for the use case
- Implement query timeout handling in application code
- Add fallback behavior for query limit exceeded scenarios
- Monitor production query performance and traversal counts

## Quality Checklist
- [ ] Query uses Oak index (explain plan shows no traversal)
- [ ] Path restriction applied and as narrow as possible
- [ ] Results limited — no unbounded queries in production
- [ ] Pagination implemented efficiently (keyset for large sets)
- [ ] Query tested with production-scale data volume
- [ ] No LIKE '%value%' patterns (use full-text instead)
- [ ] Sort clause covered by index ordering
- [ ] Fallback behavior handles query timeout/limit exceeded

## Related Skills
- aem-oak-repository-optimization (index management)
- aem-performance-tuning-profiling (query in performance context)
- aem-debugging-log-analysis (query debugging)

## Example Use Cases
1. **Content Listing Component:** Implement a dynamic content listing with QueryBuilder using path, tag, and date range filters with pagination, sorting by publish date, and result count display.
2. **Asset Search Service:** Build a DAM search service supporting metadata search, file type filtering, size ranges, and faceted results using custom Lucene index with analyzed fields for partial matching.
3. **Workflow Query Optimization:** Fix a content approval workflow querying all pending items across the repository by adding path/type restrictions, proper indexing, and result limiting to prevent traversal on 1M+ node trees.

## Notes
- QueryBuilder adds overhead compared to raw JCR-SQL2 — for performance-critical code, use SQL2 directly
- Offset-based pagination degrades linearly — keyset pagination maintains constant performance
- AEM Cloud Service enforces traversal limits strictly — queries must use indexes or they will fail
- LIKE predicates with leading wildcards ('LIKE %term%') cannot use indexes — use full-text contains()