Evidence-Grounded Eloquent Query Performance Investigation Brief

Public field comparison

Eloquent Query Performance Investigation Brief

Evidence-Grounded Eloquent Query Performance Investigation Brief

Guide Codex through slow Eloquent query investigation, N+1 detection, eager loading, counts, indexes, pagination, caching, and performance verification.

Use Codex to trace a slow Laravel execution path, diagnose Eloquent and database performance risks, compare safe remedies, and define evidence-backed regression verification.

Use this for investigating slow Laravel Eloquent queries, N+1 risks, missing indexes, eager loading gaps, expensive counts, caching risks, and verification commands.

Investigate slow Laravel pages, APIs, reports, and jobs by tracing Eloquent queries, N+1 behavior, hydration, indexes, pagination, caching, and behavior-preserving verification.

Laravel query performance triage
N+1 investigation
Eloquent eager loading review
Index and pagination risk review
Performance regression verification
Dashboard query optimization
API response performance review

Evidence-led diagnosis of slow Laravel dashboards or API endpoints
Eloquent N+1 and hidden lazy-loading investigation across resources, policies, and accessors
Query-plan and composite-index review for Laravel database workloads
Pagination, hydration, and memory-risk analysis on large datasets
Tenant-safe cache suitability and invalidation review
Before-and-after performance regression planning for Laravel changes

Slow page, endpoint, job, or workflow
Routes, controllers, models, and query paths
Tables, relationships, dataset size, and symptoms
Existing tests, allowed files, and performance target
Verification environment and profiling tools

Investigation target
Code and schema evidence
Test and verification environment
Permissions and change boundaries
Runtime and data profile
Baseline evidence and performance target

Fill in the variables with the slow page, endpoint, job, or workflow; relevant routes, controllers, models, query paths, tables, relationships, dataset size, symptoms, existing tests, allowed files, performance target, verification environment, and profiling tools. Then run the complete prompt on Codex before making performance edits. Use the output to inspect query behavior, identify N+1 risks, plan safe optimizations, review index or cache options, and verify improvements.

In Codex, replace every bracketed variable with the project-specific information. Provide the relevant Laravel source files or repository access, migrations or schema output, query logs or traces, baseline measurements, tests, environment details, and explicit command/change permissions. Then run the prompt; review the resulting investigation brief before authorizing edits, profiling, index work, caching, or deployment.

A Laravel dashboard loads slowly because each row triggers relationship queries, repeated count subqueries, expensive accessors, and unpaginated result hydration.

A multi-tenant Laravel dashboard slows as row count grows. Nested API resources trigger polymorphic relationship queries, accessors repeat aggregate checks, `withCount` produces costly correlated subqueries, and offset pagination scans increasingly deep pages. Codex traces the execution path, inventories query fingerprints, distinguishes static risks from measured findings, compares eager-loading and aggregate alternatives, evaluates a composite index against the real plan, and defines tenant-safe regression and performance acceptance checks.

Expert

Expert

Codex

Codex

performance

performance

codex
laravel
eloquent
query-performance
n-plus-one
indexes
eager-loading
caching
database
profiling
pagination
query-optimization
php

codex
laravel
eloquent
query-performance
n-plus-one
query-plans
database-indexes
pagination
caching
performance-testing
multi-tenancy
php

Eloquent Query Performance Investigation Prompt

Eloquent Query Performance Investigation Prompt

Investigate Laravel Eloquent performance with Codex, including N+1 queries, eager loading, repeated counts, indexes, pagination, caching, profiling, and verification commands.

Trace and verify Laravel Eloquent performance issues with evidence-led N+1, query-plan, index, pagination, caching, and regression analysis.

Prompt-body line comparison

Removed Added Unchanged context

−You are an expert Laravel performance engineer specializing in Eloquent, database profiling, query optimization, caching strategy, and regression-safe performance improvements.
+Investigate the supplied Laravel performance target and produce an evidence-grounded Eloquent query performance brief. Trace the complete data-access path, distinguish measured problems from hypotheses, compare optimization trade-offs, and define safe verification. Do not edit files or execute commands unless the supplied permissions explicitly authorize those actions.
 
−Inspect the supplied Laravel workflow, identify query and data-access risks, recommend safe optimizations, and define verification steps that prove performance improved without changing expected behavior.
+## Investigation inputs
 
−The goal is to help Codex investigate slow Laravel pages, APIs, jobs, dashboards, reports, or admin workflows before making risky performance edits.
+### Blocking prerequisites
 
−## Context Placeholders
+- Target and reproducible trigger: [Investigation target]
+- Relevant application, schema, and query-path material: [Code and schema evidence]
+- Safe environment, available tools, and execution permissions: [Test and verification environment]
+- Files, commands, databases, and changes that are permitted or prohibited: [Permissions and change boundaries]
 
−Use the context below. If the slow page or endpoint, query code path, or verification environment is missing, ask for it before producing the investigation brief. If other inputs are missing, continue only with clearly labeled assumptions.
+If the target cannot be identified, the relevant code cannot be inspected, or no safe verification environment is available, stop before diagnosis and request the missing prerequisite. A static review may proceed without runtime access only if it is explicitly labeled as static and unmeasured.
 
−* [Slow page, endpoint, job, or workflow]
−* [Routes, controllers, models, and query paths]
−* [Tables, relationships, dataset size, and symptoms]
−* [Existing tests, allowed files, and performance target]
−* [Verification environment and profiling tools]
+### Supporting context
 
−## Important Constraints
+- Laravel, PHP, database engine and version, queue/runtime topology, table cardinalities, tenant model, and representative data characteristics: [Runtime and data profile]
+- Existing traces, query logs, response or job timings, memory measurements, test results, service-level objective, and target threshold: [Baseline evidence and performance target]
 
−* Inspect before editing. Identify relevant routes, controllers, models, scopes, relationships, accessors, resources, jobs, views, policies, middleware, config, database assumptions, and tests.
−* Do not change unrelated files, public UI, business rules, data, generated assets, lockfiles, migrations, seeders, or out-of-scope areas unless explicitly requested.
−* Do not run destructive commands such as `git reset`, `git checkout`, `rm`, database wipes, production mutations, broad cache clears, or schema changes.
−* Do not run heavy profiling, full-table scans, expensive `EXPLAIN ANALYZE`, or load tests against production without explicit approval.
−* Protect existing behavior. Prefer characterization tests, focused regression tests, query-count checks, or before/after measurements before implementation edits.
−* Respect allowed file scopes. If required files or migrations are outside scope, explain why before touching them.
−* Separate confirmed code behavior from assumptions, likely causes, and recommendations.
−* Every performance claim must be tied to observed code, query patterns, logs, measurements, or clearly labeled assumptions.
−* Treat index changes, schema changes, cache changes, queue changes, and pagination changes as requiring human review before production use.
−* Do not introduce caching without identifying cache key, invalidation logic, stale-data risk, and acceptable freshness.
−* Do not optimize by removing authorization, tenancy checks, filters, sorting, scopes, visibility rules, or data integrity checks.
−* Provide exact verification commands and explain what each command proves.
+When supporting context is absent, preserve it as unknown. Do not invent cardinality, selectivity, query counts, execution plans, latency, memory use, cache behavior, or production equivalence. Ask for clarification when conflicting inputs could change the diagnosis—for example, different database engines, Laravel versions, tenant scopes, or pagination semantics. Otherwise, continue with bounded hypotheses and state what evidence would confirm or reject each one.
 
−## Step-by-Step Instructions
+## Codex access and action rules
 
−1. Inspect the execution path:
+Codex may inspect supplied files and, when repository or shell access is actually available, trace references and run only explicitly permitted, non-destructive commands in the approved environment. If access is unavailable, analyze only the material supplied and provide commands for a human to run. Never imply that Codex opened a file, executed a query, measured a baseline, changed code, or passed a test unless the corresponding evidence exists in the session.
 
−   * route
−   * controller
−   * action or handler
−   * model queries
−   * global scopes
−   * local scopes
−   * relationships
−   * accessors and mutators
−   * API resources or transformers
−   * Blade loops or components
−   * Livewire or Inertia props if relevant
−   * jobs or queued work if relevant
−   * policies or tenant filters
−   * tests
+Do not mutate production data or schema, deploy, merge, approve, clear shared caches, restart services, run broad load tests, or use destructive Git or database commands. Do not run `EXPLAIN ANALYZE`, unbounded diagnostic queries, full-table scans, or high-volume profiling against production without explicit authorization and an assessed resource budget. Redact credentials, tokens, personal data, raw session content, and sensitive query bindings from the brief.
 
−2. Identify query behavior:
+Treat migrations, new indexes, cache introduction, queue changes, pagination-semantic changes, denormalization, and production profiling as separate proposals requiring human review. Preserve authorization, policies, global scopes, tenant isolation, soft-delete behavior, visibility filters, ordering, serialization shape, totals, and consistency requirements. Stop and escalate if a proposed optimization could weaken any of them or if the available environment is not safe for verification.
 
−   * total query count
−   * duplicate queries
−   * N+1 relationship queries
−   * lazy-loaded relationships
−   * repeated `count()` calls
−   * repeated `exists()` checks
−   * expensive accessors that query the database
−   * queries inside loops
−   * unbounded `get()` calls
−   * missing pagination
−   * offset pagination risk
−   * unnecessary selected columns
−   * expensive ordering
−   * expensive filtering
−   * heavy joins
−   * subqueries
−   * full table scans
−   * large result hydration
−   * memory-heavy collection operations
+## Evidence vocabulary
 
−3. Review Eloquent optimization options:
+Assign each material item an evidence ID and classify it as one of the following:
 
−   * eager loading with `with()`
−   * constrained eager loading
−   * `withCount()`
−   * `withExists()`
−   * `loadMissing()`
−   * selected columns
−   * query scopes
−   * pagination
−   * cursor pagination where appropriate
−   * chunking or lazy collections for jobs
−   * moving repeated calculations out of loops
−   * replacing per-row queries with aggregate queries
−   * avoiding accidental eager loading of large relationships
−   * preserving authorization and tenant boundaries
+- **Supplied fact:** stated in the inputs but not independently observed.
+- **Static observation:** directly supported by a cited file, symbol, migration, configuration value, or test.
+- **Execution evidence:** produced by an authorized command, trace, query log, plan, or measurement; include environment and command or capture method.
+- **Hypothesis:** plausible explanation awaiting a discriminating check.
+- **Assumption:** temporary premise required to continue.
+- **Unknown:** unavailable information that affects confidence.
+- **Conflict:** incompatible evidence that must be reconciled.
 
−4. Review database optimization options:
+Cite file paths and symbols for static observations. For runtime evidence, record environment, dataset representativeness, warm or cold state, sample count, statistic used, query capture method, and timestamp when available. Do not treat Laravel Debugbar, Telescope, Clockwork, database logs, APM traces, or test query listeners as interchangeable; note their capture scope and overhead.
 
−   * existing indexes
−   * missing index candidates
−   * composite index candidates
−   * index column order
−   * foreign key indexes
−   * sort and filter indexes
−   * `EXPLAIN` output if available
−   * query plan risk
−   * migration requirement
−   * DBA or owner review need
−   * production rollout risk
+Use only these work states: **proposed**, **authorized but not executed**, **executed with evidence**, **blocked**, and **not applicable**. Reserve **measured**, **verified**, **fixed**, and **tests passed** for executed work with cited before-and-after or test evidence. Never claim deployment, approval, merge, or production validation unless explicit evidence is supplied.
 
−5. Review cache options:
+## Investigation workflow
 
−   * whether caching is appropriate
−   * cache key design
−   * cache scope
−   * user or tenant isolation
−   * invalidation trigger
−   * TTL
−   * stale data risk
−   * cache stampede risk
−   * permission or privacy risk
−   * rollback plan
+### 1. Establish the measurement contract
 
−6. Prioritize findings:
+Define the user-visible or operational trigger, expected behavior, performance metric, threshold, representative dataset, concurrency assumptions, cache state, database state, number of samples, and comparison method. Separate application latency from database time, serialization time, network time, queue delay, and client rendering where evidence permits. If no usable baseline exists, design one rather than describing improvement.
 
−   * confirmed performance issue
−   * likely impact
−   * implementation risk
−   * behavior-change risk
−   * verification difficulty
−   * production rollout risk
−   * human review required
+### 2. Trace the Laravel execution path
 
−7. Create a safe remediation sequence:
+Map the route, middleware, controller or invokable action, form or DTO input, service or repository, model builders, local and global scopes, policies, tenant constraints, relationship methods, accessors or casts, API resources, Blade components, Livewire or Inertia serialization, events, observers, queued jobs, and tests that influence the target.
 
−   * measure baseline first
−   * add tests or characterization checks
−   * apply the smallest code change
−   * verify query count and response time
−   * review database index changes separately
−   * review caching separately
−   * document assumptions and residual risks
+Record where a query builder is created, cloned, constrained, executed, and hydrated. Check for hidden query triggers in accessors, appended attributes, resource conditionals, policy checks, collection callbacks, view loops, model events, and serialization. Include package-owned behavior only when evidenced.
 
−8. Provide verification steps:
+### 3. Build a query inventory
 
−   * focused test commands
−   * query-count checks
−   * before/after profiling
−   * `EXPLAIN` checks where safe
−   * route smoke tests
−   * feature tests
−   * cache behavior checks
−   * regression checks for authorization, tenant boundaries, filters, sorting, and pagination
+For each distinct query fingerprint, record call site, execution count, bindings shape with sensitive values removed, selected columns, joins or subqueries, filters, order, limit, returned rows, scanned rows when known, duration distribution, and consumers. Look specifically for:
 
−## Output Format
+- relationship or polymorphic N+1 behavior, including nested resources and `morphTo` loading;
+- lazy loading hidden by accessors, policies, Blade, resources, or appended attributes;
+- repeated `count`, `exists`, aggregate, authorization, tenant, or lookup queries;
+- correlated `withCount` or `withExists` subqueries whose cost scales poorly;
+- unbounded `get`, `all`, relationship hydration, large `IN` lists, and memory-heavy collection transforms;
+- accidental over-fetching, duplicated model hydration, broad eager loads, and selected-column mistakes that omit relationship keys;
+- non-sargable predicates, leading-wildcard searches, functions or casts on indexed columns, implicit type conversions, and `OR` conditions that defeat useful access paths;
+- expensive joins, `whereHas` or nested existence checks, `distinct`, grouping, sorting, temporary tables, and filesorts;
+- offset pagination degradation and cursor pagination incompatibility with non-unique or unstable ordering;
+- lock waits, connection-pool pressure, read/write routing, transaction scope, replica lag, or queue amplification when relevant.
 
−### 1. Missing Context
+Do not label a code pattern an N+1 incident without execution evidence or a precise static trigger showing query count scales with result size. If only static evidence exists, call it an N+1 risk and prescribe a query-count check.
 
−List missing inputs needed before a reliable Eloquent performance investigation can be completed. If enough context is available, say so.
+### 4. Evaluate Eloquent remedies
 
−### 2. Execution Path Map
+Compare the smallest behavior-preserving options: constrained `with`, nested eager loading, `morphWith`, `loadMissing`, `withCount`, `withExists`, aggregate joins, precomputed maps, moving invariant work outside loops, column projection, chunking, lazy iteration, simple pagination, or cursor pagination. Check each option for query count, total database work, row multiplication, memory use, model-event behavior, serialization shape, and scope preservation.
 
−Use this table:
+Verify that projected parent and child columns retain primary and foreign keys needed for relationship matching. Avoid replacing an N+1 pattern with one enormous eager load or an aggregate query that performs worse at real cardinality. Treat `Model::preventLazyLoading` as a development or test diagnostic whose environment behavior must be reviewed, not as proof that all query paths are safe.
 
−| Layer | File or Area | Observed Behavior | Performance Risk | Evidence |
−| ----- | ------------ | ----------------- | ---------------- | -------- |
+For pagination, compare offset, simple, and cursor semantics. Cursor pagination requires deterministic ordering with a unique tie-breaker and may affect page-number navigation, totals, deep links, and concurrent-write behavior. Record these product-level trade-offs instead of presenting it as a mechanical replacement.
 
−Cover routes, controllers, models, scopes, relationships, views/resources, jobs, policies, middleware, config, and tests where relevant.
+### 5. Evaluate database access paths
 
−### 3. Query Behavior Map
+Inspect migrations or authoritative schema evidence before recommending an index. Reconcile model casts and query bindings with actual column types and collations. For each candidate index, connect its column order to equality predicates, range predicates, joins, and ordering; consider the database engine's leftmost-prefix behavior, selectivity, covering potential, existing overlapping indexes, index width, write amplification, storage, lock or online-build implications, and rollback strategy.
 
−Use this table:
+Interpret `EXPLAIN` in the context of the named database engine and version. Capture plan fields relevant to that engine, estimated versus actual rows only when safely available, access method, chosen and possible indexes, join order, sort or temporary operations, and predicate filtering. A plan estimate alone does not prove latency improvement. Never recommend dropping an index solely because it was not selected in one captured plan.
 
−| Query Area | Current Pattern | Risk | Evidence Needed | Suggested Check |
−| ---------- | --------------- | ---- | --------------- | --------------- |
+### 6. Evaluate caching only after query remedies
 
−Cover N+1 risks, duplicate queries, counts, accessors, pagination, filtering, sorting, joins, subqueries, and result hydration.
+For every cache candidate, define the value, key dimensions, tenant and user isolation, authorization sensitivity, TTL or freshness contract, invalidation events, transaction timing, tag support, stampede control, negative caching behavior, payload size, backend limits, observability, failure behavior, and rollback. Reject caching when invalidation cannot preserve required correctness, when keys could leak cross-tenant data, or when it would conceal an unbounded query.
 
−### 4. Performance Findings
+### 7. Rank and sequence remedies
 
−Use this table:
+Rank findings by evidence strength, expected impact, behavior risk, operational risk, implementation effort, and verification cost. Prefer reversible code-level changes backed by focused tests before schema or cache changes. Separate independent remedies so their effects can be measured. Include stop conditions and a rollback trigger for every executable recommendation.
 
−| Finding | Evidence | Impact | Confidence | Risk if Changed |
−| ------- | -------- | ------ | ---------- | --------------- |
+### 8. Design verification and acceptance
 
−Separate confirmed findings from likely causes and assumptions.
+For each proposed change, specify the exact safe command or capture procedure, prerequisites, expected observation, actual observation if executed, evidence ID, acceptance threshold, and unresolved state. Use project-discovered PHPUnit or Pest test paths and actual route or command names; do not fabricate them. Verification should cover, where applicable:
 
−### 5. Optimization Plan
+- stable query fingerprints and reduced query-count growth across at least two result sizes;
+- before-and-after latency and memory under equivalent dataset, cache, runtime, and sampling conditions;
+- plan changes and row-access evidence for index proposals;
+- response schema, ordering, filters, totals, null handling, and pagination boundaries;
+- policy, tenant, soft-delete, visibility, and user-specific behavior;
+- cache hit, miss, invalidation, isolation, stale-data, stampede, and backend-failure behavior;
+- job chunk boundaries, retries, idempotency, and peak memory;
+- targeted tests plus the smallest relevant regression suite.
 
−Use this table:
+If results are noisy, contradictory, not comparable, or below the declared threshold, report the remedy as unverified and prescribe reconciliation. Do not convert an absent regression into proof of performance improvement.
 
−| Optimization | Why It Helps | Files or Areas | Behavior Risk | Priority |
−| ------------ | ------------ | -------------- | ------------- | -------- |
+## Required deliverable
 
−Include code-level changes only where evidence supports them.
+Produce the following markdown sections.
 
−### 6. Database and Index Notes
+### A. Intake and investigation state
 
−Use this table:
+State the target, trigger, blocking gaps, useful unknowns, permissions, environment safety, and whether the brief is static-only or includes execution evidence. Include a ledger with columns: Evidence ID, Classification, Source or command, Environment, Observation, Limitations.
 
−| Table or Query | Index or Schema Consideration | Evidence | Review Needed | Production Risk |
−| -------------- | ----------------------------- | -------- | ------------- | --------------- |
+### B. Measurement contract
 
−State clearly that index and schema changes require review before production use.
+Use columns: Metric, Baseline method, Dataset and cache state, Sample plan, Target threshold, Existing evidence, Comparability risks. Record absent baselines explicitly.
 
−### 7. Cache Suitability Review
+### C. Laravel execution and query-trigger map
 
−Use this table:
+Use columns: Stage, File and symbol, Builder or relationship, Execution trigger, Scope or authorization effect, Evidence ID, Performance concern. Trace through rendering, serialization, events, or jobs when relevant.
 
−| Candidate | Cache Key or Scope | Invalidation Need | Stale Data Risk | Recommendation |
−| --------- | ------------------ | ----------------- | --------------- | -------------- |
+### D. Query fingerprint inventory
 
−If caching is not appropriate, say why.
+Use columns: Query ID, Call site, Pattern and execution count, Rows returned or examined, Timing evidence, Scaling factor, Hydration or memory effect, Evidence ID. Redact sensitive bindings and mark unavailable values unknown.
 
−### 8. Regression Test Plan
+### E. Findings and discriminating checks
 
−Use this table:
+Use columns: Finding ID, Classification, Evidence IDs, Mechanism, Expected impact, Confidence, Behavior risk, Check that confirms or rejects it. Separate confirmed issues, hypotheses, assumptions, unknowns, and conflicts.
 
−| Test Scenario | Purpose | Suggested Test Name | Expected Result |
−| ------------- | ------- | ------------------- | --------------- |
+### F. Eloquent remedy decision matrix
 
−Include behavior tests and performance-focused checks where possible.
+Use columns: Finding ID, Candidate remedy, Query and memory trade-off, Scope or serialization risk, Pagination or consistency effect, Required files, Verification, Recommendation. Include rejected alternatives and why they are inferior for this target.
 
−### 9. Remediation Sequence
+### G. Database plan and index review
 
−Provide a step-by-step plan that starts with measurement and tests, then applies the smallest safe change, then verifies performance and behavior.
+Use columns: Query ID, Current plan evidence, Candidate index or rewrite, Column-order rationale, Overlap and write cost, Build and rollback risk, Required approval, Decision state. Keep schema changes proposed unless execution was explicitly authorized and evidenced.
 
−### 10. Verification Commands
+### H. Cache suitability and isolation review
 
−List exact commands and explain what each command proves. Include safe Laravel, PHPUnit/Pest, route, profiling, and database-inspection commands where appropriate.
+Use columns: Candidate, Key dimensions, Freshness contract, Invalidation events, Tenant and permission isolation, Stampede or failure handling, Observability, Decision. State clearly when caching is unsuitable.
 
−### 11. Assumptions and Human Checks
+### I. Prioritized remediation sequence
 
−List assumptions made, unresolved risks, missing measurements, confidence level, and human checks required before editing queries, adding indexes, changing pagination, or introducing caching.
+Provide numbered, independently measurable steps. For each step include finding IDs, work state, permitted action, expected effect, behavior risk, stop condition, rollback trigger, and human approval point. Begin with baseline and characterization evidence.
 
−## Verification Checklist
+### J. Verification and acceptance matrix
 
−Before finalizing, confirm that:
+Use columns: Change or hypothesis, Exact command or procedure, Preconditions, Expected observation, Actual observation, Evidence ID, Acceptance threshold, Result state. If nothing was run, every actual observation must say not executed and results must remain proposed or blocked.
 
−* every performance claim is tied to observed code, query patterns, logs, measurements, or labeled assumptions
−* behavior, authorization, tenant boundaries, filters, sorting, and pagination are protected
−* N+1 risks and lazy-loaded relationships are checked
−* repeated counts, accessors, resources, views, and loops are reviewed
−* missing indexes are recommendations, not automatic schema changes
−* caching includes key design, invalidation, freshness, and privacy review
−* production profiling and load testing require approval
−* verification commands are exact and explain what they prove
−* confirmed behavior is separated from assumptions
−* missing inputs and unresolved risks are listed
+### K. Completion and handoff register
 
−## Final Instruction to Begin
+List files changed, commands run, tests run, measurements captured, migrations proposed, cache changes proposed, approvals obtained, and deployments performed. For each item give its work state and evidence ID. Use `none` or `not executed` where appropriate; never infer completion. Finish with unresolved risks, evidence still needed, confidence by finding, responsible human decisions, and the safest next action.
 
−Begin now. First review the supplied slow page, endpoint, job, or workflow; routes; controllers; models; query paths; tables; relationships; dataset size; symptoms; existing tests; allowed files; performance target; verification environment; and profiling tools. If required context is missing, ask for it. Otherwise, produce the full Eloquent query performance investigation brief in the requested markdown format.
+Before returning the brief, reconcile all findings with their evidence IDs, ensure every advertised improvement has an acceptance check, and remove any completion verb that is unsupported by execution evidence.