lc-slow-queries

name	lc:slow-queries
description	Detect queries without indexes, queries in loops, and unbounded selects.
argument-hint	[analyze | fix | fix --dry-run | file-path | fix file-path]
user-invocable	true
allowed-tools	Read Grep Bash Edit Write Glob

Slow Query Detection

Detect potential slow queries through static analysis: missing indexes, queries inside loops, unbounded selects, and foreign key columns without indexes.

Subcommands

Subcommand	Description
(no argument)	Scan the entire project for slow query patterns. Read-only report.
fix	Generate migration(s) to add missing indexes. Asks for confirmation before each change.
fix --dry-run	Show what index migrations would be generated without writing anything.
[file-path]	Analyze a specific file for slow query patterns. Read-only.
fix [file-path]	Fix slow query issues originating from a specific file, with confirmation.

Detection Rules

1. Missing Indexes on Queried Columns

What to detect: Columns used in where(), whereHas(), orderBy(), groupBy(), and having() clauses that lack a corresponding index in migrations.

How to scan:

1. Use Grep to find all query builder calls with column references:
   • ->where('column_name', and ->where(['column_name' =>
   • ->whereHas('relation', (check the closure for column references)
   • ->orderBy('column_name')
   • ->groupBy('column_name')
   • ->having('column_name',
2. For each column found, check migrations for:
   • $table->index('column_name') or $table->index(['column_name', ...])
   • $table->unique('column_name') (unique indexes also serve as indexes)
   • Column defined with ->primary(), ->unique(), or ->index()
   • $table->foreignId('column_name') -- some databases auto-index FK columns, some don't
3. Flag columns used in WHERE/ORDER without any index.

Severity: Medium (depends on table size)

Fix: Generate a migration to add the missing index.

2. Queries Inside Loops

What to detect: Database queries executed inside foreach, for, while, array_map, collect()->each(), or similar iteration constructs.

How to scan:

1. Use Grep to find loop constructs in PHP files.
2. Use Read to load each file with loops and check for query builder or Eloquent calls inside the loop body:
   • Model::where(, Model::find(, Model::create(
   • DB::table(, DB::select(
   • ->save(), ->update(), ->delete()
   • ->first(), ->get(), ->count()
3. Distinguish between:
   • N+1 via query: Direct query calls inside loops (this skill's scope)
   • N+1 via relationship: Lazy-loaded relationships (handled by lc:find-n-plus-one)

Severity: High (multiplied by loop iteration count)

Fix: Suggest refactoring to bulk operations:

• Model::find($id) in loop -> Model::whereIn('id', $ids)->get()->keyBy('id')
• ->save() in loop -> Model::upsert($records, ...)
• ->create() in loop -> Model::insert($records)

3. Unbounded Selects

What to detect: Queries using ->get() without ->limit(), ->take(), or ->paginate() on tables that could grow large.

How to scan:

1. Use Grep to find all ->get() calls (excluding ->get('key') which is Collection::get).
2. Trace back the query chain to check for:
   • ->limit() or ->take() present -> safe
   • ->paginate() or ->simplePaginate() present -> safe
   • ->where() with a highly selective condition (e.g., where('id', $id)) -> safe
   • None of the above -> potentially unbounded
3. Cross-reference with table size hints:
   • Tables with timestamps() and many migration modifications are likely large
   • Pivot tables and log/activity tables grow rapidly

Severity: Medium (could cause memory issues on large tables)

Fix: Suggest adding ->limit() or converting to ->paginate().

4. Missing Indexes on Foreign Key Columns

What to detect: Foreign key columns defined in migrations without explicit indexes. While some databases (MySQL InnoDB) auto-create indexes for FK constraints, columns used as FKs via convention (ending in _id) but without actual FK constraints will not have indexes.

How to scan:

1. Parse all migrations for columns ending in _id.
2. Check if each _id column has:
   • A ->foreign() constraint (MySQL auto-indexes these)
   • An explicit ->index() definition
   • A ->foreignId() call (creates both column and index via constraint)
3. Flag _id columns that have neither a FK constraint nor an explicit index.

Severity: Medium

Fix: Generate a migration to add indexes on unindexed FK columns.

5. SELECT * Patterns

What to detect: Queries that select all columns when only a few are needed, especially on wide tables.

How to scan:

1. Use Grep to find queries without ->select() on tables with many columns (15+).
2. Check for Model::all() usage.
3. Check for ->get() without a preceding ->select().

Severity: Low (performance impact varies by table width)

Fix: Suggest adding ->select() with only needed columns, especially in list/index views.

Report Format

SLOW QUERY ANALYSIS
====================

CRITICAL (queries in loops)
----------------------------
1. Query inside foreach loop
   File: app/Services/PropertyService.php:78
   Loop: foreach ($propertyIds as $id)
   Query: Property::find($id)
   Impact: ~100 queries per request (estimated from context)
   Fix: Refactor to Property::whereIn('id', $propertyIds)->get()

HIGH (missing indexes on frequently queried columns)
-----------------------------------------------------
1. Missing index on 'properties.status'
   Queried in: 4 files
   - app/Http/Controllers/PropertyController.php:32
   - resources/views/livewire/properties/index.blade.php:15
   - app/Services/SearchService.php:44
   - app/Jobs/SyncPropertyJob.php:22
   Table estimated size: Large (15+ migrations)
   Fix: Add index migration

MEDIUM (unbounded selects)
---------------------------
1. Unbounded ->get() on 'activities' table
   File: app/Http/Controllers/DashboardController.php:18
   Query: Activity::where('organization_id', $id)->get()
   Fix: Add ->paginate(50) or ->limit(100)

SUMMARY
========
Issues found: 12
  Critical (queries in loops):     3
  High (missing indexes):          5
  Medium (unbounded selects):      3
  Low (SELECT * patterns):         1

Fix Mode

In fix mode, generate migrations for missing indexes:

1. Group all missing indexes by table.
2. Generate one migration per table:

   Schema::table('properties', function (Blueprint $table) {
       $table->index('status');
       $table->index('organization_id');
   });
   

3. Write the migration file with appropriate timestamp.
4. Validate syntax.

For queries-in-loops and unbounded selects, show the suggested refactoring code but do not auto-apply (risk of breaking logic). Present as recommendations with file path and line number.

Notes

• This is static analysis. Actual query performance depends on data volume, database configuration, and query plans.
• Use EXPLAIN on actual queries for definitive performance analysis.
• Some "unbounded" queries may be intentionally loading small datasets. Review context before adding limits.
• Foreign key columns created with $table->foreignId() automatically get indexes in MySQL via the FK constraint. These are safe.
• Composite indexes may be more effective than single-column indexes for queries with multiple WHERE conditions. Note this in recommendations.