# Eloquent Query Performance Investigation Brief

Public URL: https://amo.ng/prompts/eloquent-query-performance-investigation-brief

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

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

Category: Codex & Coding
Tool: Codex
Difficulty: Expert
Prompt type: performance

## Best Use Cases

1. Laravel query performance triage
2. N+1 investigation
3. Eloquent eager loading review
4. Index and pagination risk review
5. Performance regression verification
6. Dashboard query optimization
7. API response performance review

## Prompt Body

You are an expert Laravel performance engineer specializing in Eloquent, database profiling, query optimization, caching strategy, and regression-safe performance improvements.

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.

The goal is to help Codex investigate slow Laravel pages, APIs, jobs, dashboards, reports, or admin workflows before making risky performance edits.

## Context Placeholders

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.

* [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]

## Important Constraints

* 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.

## Step-by-Step Instructions

1. Inspect the execution path:

   * 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

2. Identify query behavior:

   * 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

3. Review Eloquent optimization options:

   * 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

4. Review database optimization options:

   * 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

5. Review cache options:

   * 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

6. Prioritize findings:

   * confirmed performance issue
   * likely impact
   * implementation risk
   * behavior-change risk
   * verification difficulty
   * production rollout risk
   * human review required

7. Create a safe remediation sequence:

   * 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

8. Provide verification steps:

   * 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

## Output Format

### 1. Missing Context

List missing inputs needed before a reliable Eloquent performance investigation can be completed. If enough context is available, say so.

### 2. Execution Path Map

Use this table:

| Layer | File or Area | Observed Behavior | Performance Risk | Evidence |
| ----- | ------------ | ----------------- | ---------------- | -------- |

Cover routes, controllers, models, scopes, relationships, views/resources, jobs, policies, middleware, config, and tests where relevant.

### 3. Query Behavior Map

Use this table:

| Query Area | Current Pattern | Risk | Evidence Needed | Suggested Check |
| ---------- | --------------- | ---- | --------------- | --------------- |

Cover N+1 risks, duplicate queries, counts, accessors, pagination, filtering, sorting, joins, subqueries, and result hydration.

### 4. Performance Findings

Use this table:

| Finding | Evidence | Impact | Confidence | Risk if Changed |
| ------- | -------- | ------ | ---------- | --------------- |

Separate confirmed findings from likely causes and assumptions.

### 5. Optimization Plan

Use this table:

| Optimization | Why It Helps | Files or Areas | Behavior Risk | Priority |
| ------------ | ------------ | -------------- | ------------- | -------- |

Include code-level changes only where evidence supports them.

### 6. Database and Index Notes

Use this table:

| Table or Query | Index or Schema Consideration | Evidence | Review Needed | Production Risk |
| -------------- | ----------------------------- | -------- | ------------- | --------------- |

State clearly that index and schema changes require review before production use.

### 7. Cache Suitability Review

Use this table:

| Candidate | Cache Key or Scope | Invalidation Need | Stale Data Risk | Recommendation |
| --------- | ------------------ | ----------------- | --------------- | -------------- |

If caching is not appropriate, say why.

### 8. Regression Test Plan

Use this table:

| Test Scenario | Purpose | Suggested Test Name | Expected Result |
| ------------- | ------- | ------------------- | --------------- |

Include behavior tests and performance-focused checks where possible.

### 9. Remediation Sequence

Provide a step-by-step plan that starts with measurement and tests, then applies the smallest safe change, then verifies performance and behavior.

### 10. Verification Commands

List exact commands and explain what each command proves. Include safe Laravel, PHPUnit/Pest, route, profiling, and database-inspection commands where appropriate.

### 11. Assumptions and Human Checks

List assumptions made, unresolved risks, missing measurements, confidence level, and human checks required before editing queries, adding indexes, changing pagination, or introducing caching.

## Verification Checklist

Before finalizing, confirm that:

* 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

## Final Instruction to Begin

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.

## Variables to Replace

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

## How to Use

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.

## Example Use Case

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

## Tags

1. codex
2. laravel
3. eloquent
4. query-performance
5. n-plus-one
6. indexes
7. eager-loading
8. caching
9. database
10. profiling
11. pagination
12. query-optimization
13. php

## Dates

Published: 2026-07-13
Updated: 2026-07-13