mysql-problem-solver

MySQL Problem Solver

Purpose

Investigate real MySQL performance or query design problems in existing applications.

Focus on:

• the actual query
• real schema and index usage
• EXPLAIN-based diagnosis when possible
• safe, justified optimizations

Constraints

• Apply @rules/sql.mdc
• If the current project uses Laravel, also apply @rules/laravel/laravel.mdc, @rules/laravel/architecture.mdc, @rules/laravel/filament.mdc, and @rules/laravel/livewire.mdc
• Be practical and direct
• Prefer investigation over assumptions
• Do not invent schema, indexes, or runtime behavior
• Do not recommend index changes without explaining why they help
• Apply @rules/sql/optimalize.mdc "Performance Non-Regression on Query Changes" — every proposed query rewrite must be at least as fast as the original (ideally faster); a proposal that is slower must carry the documented reason and the remaining optimization options
• If DB access is unavailable, continue with static analysis and state the limitation clearly

Execution

1. Identify the Query

• Find the actual SQL or reconstruct it from Laravel/Eloquent/query builder code
• Include filters, joins, ordering, grouping, pagination, and subqueries

2. Inspect Schema

• Inspect relevant tables and indexes using:
  • schema output
  • migrations
  • model relationships
  • DB tools when available

3. Run EXPLAIN

• If MySQL access is available, run EXPLAIN
• Review:
  • table
  • type
  • possible_keys
  • key
  • rows
  • filtered
  • Extra

4. Diagnose the Problem

Look for:

• full scans
• weak join strategy
• existing index bypassed — query could hit a covering index already in the schema but the column order, a wrapping function, or extra projected columns prevent it. Preferred fix is a query rewrite (column re-ordering, SARGable rewrite, covering projection), not a new index (see @rules/sql/optimalize.mdc "Reuse existing indexes first").
• missing or ineffective indexes
• non-SARGable filters
• poor sort/group plans
• offset pagination on large datasets
• N+1 behavior from application code
• per-row queries inside loops — per-row update() / create() / delete() or single-row reads driven by a foreach (distinct from N+1 eager-loading: this is application code intentionally writing or reading row-by-row when a single batch query would suffice)
• redundant or overlapping indexes
• charset / collation mismatch between compared or joined columns — the implicit conversion makes the converted side non-SARGable (its index is skipped), and an ascii column compared against a non-ASCII value raises ERROR 1267 instead of returning no rows
• schema-level causes of a slow plan (see @rules/sql/optimalize.mdc "Schema Design"): an oversized primary key copied into every secondary index, an unjustified VARCHAR(255) inflating sort buffers and temporary tables, TIMESTAMP columns converting per row by session time zone, or FLOAT / DOUBLE where DECIMAL belongs