Skip to content
Skills Library
Data & analytics · Analysis

mysql-problem-solver

/mysql-problem-solver

Use when analyze real MySQL query and schema problems using code inspection, schema review, and EXPLAIN when available

Licence
MIT
Last repository change
03.10.2026

Assessment and security

90/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content25 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
2
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 03.10.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Use when analyze real MySQL query and schema problems using code inspection, schema review, and EXPLAIN when available

Original author description

Use when analyze real MySQL query and schema problems using code inspection, schema review, and EXPLAIN when available

How it works

  1. Purpose
  2. Constraints
  3. Execution
  4. Laravel-Specific Checks
  5. Terminal Guidance
  6. Output Format
  7. Principles
Original SKILL.md headings · 135 lines, 1,149 words
SKILL.md preview
---
name: mysql-problem-solver
description: Use when analyze real MySQL query and schema problems using code
  inspection, schema review, and EXPLAIN when available
license: MIT
metadata:
  author: Petr Král (pekral.cz)
---

# 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

### 5. Propose Optimizations

Before recommending any rewrite, capture the **baseline** of the original query (`EXPLAIN` / `EXPLAIN ANALYZE` — `type`, `key`, `rows`, `filtered`, `Extra`, measured latency when DB access is available). Every proposal must then be held against that baseline per `@rules/sql/optimalize.mdc` "Performance Non-Regression on Query Changes":

- The rewritten query must be **equal or better** on rows examined, access `type`, index usage, `filesort` / `temporary` avoidance, and latency.
- If a proposal is unavoidably **slower** than the original (e.g. a correctness fix that widens the row set), do not present it as a clean win — state **why it is slower**, list the **remaining optimization options** (or state that none exist and why), and the **trade-off that justifies it**.

Recommend only justified changes, such as:
- query rewrite to reuse an existing schema index (preferred — verify which indexes already exist via migrations / `SHOW INDEX` before proposing a new one; reorder `WHERE` / `JOIN` / `ORDER BY` columns to match an existing composite index, drop functions wrapping indexed columns, and project only columns the index already covers)
- query rewrite (general)
- Eloquent/query builder rewrite
- eager loading change
- pagination change
- batching per-row loops into a single bulk operation — ModelManager batch methods (`batchUpdate`, `batchInsert`), `whereIn(...)->delete()` for deletes, or one bulk read keyed in memory for lookups (see `@rules/sql/optimalize.mdc` "Batch over per-row operations")
- index addition or replacement (only when the existing schema cannot cover the query and EXPLAIN confirms the gap after the rewrite alternative has been ruled out)
- redundant index removal
- column type / charset / collation alignment when the mismatch is what blocks the index (`@rules/sql/optimalize.mdc` "Schema Design")
- splitting one query into smaller ones

Explain trade-offs:
- write overhead
- duplicate indexes
- over-indexing
- complexity vs benefit

### 6. Assess Deployment Safety of Schema Changes

Run this step whenever the input includes DDL — a migration file, `Schema::create` / `Schema::table` / `Schema::drop*` / `Schema::rename*`, a `DB::statement` carrying DDL, or raw `ALTER TABLE` / `CREATE INDEX`. A statement that is instant on an empty dev database can lock a populated production table, break the release still serving traffic, or fail halfway with no way back, so judge every statement against `@rules/sql/optimalize.mdc` *Deployment Safety of Schema Changes*:

- destructive change (column / table drop or rename, narrowed type, tightened `ENUM`) shipped in the same release as the code that reads the old surface — propose the expand / contract split
- blocking DDL: the change can only run as `ALGORITHM=COPY`, or an index build states no algorithm / lock mode — propose the explicit `ALGORITHM=INSTANT` / `ALGORITHM=INPLACE, LOCK=NONE` statement, or the `pt-online-schema-change` / `gh-ost` command
- `down()` missing, empty, or not restoring the prior structure, and structure changes with no `Schema::has*()` guard to survive a replay after a failed deploy
- a data backfill inside the migration — propose the DDL-only migration plus a chunked, resumable, idempotent command / queued job
- a new `NOT NULL` / `UNIQUE` / `CHECK` / foreign key on a populated table with no pre-flight — propose the counting query that finds the violating rows, then remediation, then the constraint
- a foreign key or a newly queried column with no backing index shipped in the same release — propose the exact index DDL

Report each one as a finding with its concrete artifact, the same way step 5 reports an optimization.

## Laravel-Specific Checks
When the input is Laravel code, also inspect:
- `with()` / eager loading
- `whereHas()` / nested filters
- `withCount()`
- `chunk()` vs `cursor()` vs pagination
- scopes hiding query complexity
- repeated queries in loops

## Terminal Guidance
When terminal access is available, inspect DB connection details from:
- `.env`
- `config/database.php`
- docker/dev setup

Use MySQL tools when possible for:
- `SHOW CREATE TABLE`
- `SHOW INDEX`
- `EXPLAIN`

If access fails, continue statically and say so.

## Output Format

Use the template defined in `templates/analysis-report.md`.
---

## Principles

- Focus on the real bottleneck, not generic SQL advice
- Prefer evidence from EXPLAIN over assumptions
- Validate schema and index usage before proposing changes
- Avoid unnecessary or duplicate indexes
- Explain trade-offs (read vs write cost, complexity vs benefit)
- Be concise, practical, and explicit about limitations
Files in this package (2)
  • SKILL.md 8 KB
  • templates/analysis-report.md 1 KB

Versions

The database has no other skills named mysql-problem-solver.

Database snapshot: 13.11.2026 · package version 6bb7dfc84d79 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 2 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100mcp-server-mysql
    Use this skill when installing, configuring, and executing tools with the MySQL database schema inspection and parameterized query MySQL MCP server.
  • 84analyze-mysql-schema
    Analyzes MySQL database schema and generates a comprehensive Markdown report for sharing. Use when you need to document MySQL schema objects including tables, triggers, stored procedures, views, and object dependencies.
  • 92write-script-mysql
    MUST use when writing MySQL queries.
  • 90mysql
    Plan and review MySQL/InnoDB schema, indexing, query tuning, transactions, and operations. Use when creating or modifying MySQL tables, indexes, or queries; diagnosing slow/locking behavior; planning migrations; or troubleshooting replication and connection issues. Load when using a MySQL database.
  • 90mysql
    Design, query, migrate, or troubleshoot a MySQL-backed application. Use when dealing with schema, indexes, transactions, isolation, replication, backups, or MySQL-specific behavior.
  • 86mysql
    MySQL开发专家助手。当用户需要进行MySQL数据库设计、SQL优化、索引调优、分库分表或数据库运维时调用。

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.