mysql-patterns

MySQL Patterns

Constraints

• Apply @rules/sql/optimalize.mdc — it already owns indexing, SARGable WHERE, seek/keyset pagination, EXPLAIN, transactions/locking basics, batch-over-per-row, CTE/window/recursive queries, schema basics, DB-level caching, and the Performance Non-Regression on Query Changes gate. Do not re-explain those here; defer to it. When a pattern in this skill changes an existing query (e.g. swapping LIKE for a FULLTEXT match, moving a filter onto a generated column, introducing partition pruning), capture the original query's baseline and confirm the new shape is equal or faster — if it is slower, document the reason and the remaining optimization options per that gate.
• For diagnosing an existing slow query, use @skills/mysql-problem-solver/SKILL.md. This skill is for designing features, not investigating regressions.
• If the project uses Laravel, also apply @rules/laravel/laravel.mdc and @rules/laravel/architecture.mdc.
• Apply @rules/security/backend.md — parameterized queries / ORM only, least-privilege DB users, never hardcode credentials.
• final classes, declare(strict_types=1), Pest tests for any data-access code added.
• Verify the engine/version before using a version-specific feature (SELECT VERSION();). MySQL 8 and MariaDB diverge on JSON, ON DUPLICATE KEY aliases, and SKIP LOCKED.

Use when

• Adding upserts, JSON columns, full-text search, generated columns, or partitioning to a Laravel app.
• Writing a migration that creates a new table and needs the schema standard expressed in Blueprint.
• Setting up read/write splitting against replicas, or hardening against deadlocks and connection exhaustion.
• Reviewing a migration that introduces any of the above on a large table.

These topics complement the query-tuning rules; they are not covered there.

Schema Standard in Laravel Migrations

@rules/sql/optimalize.mdc Schema Design owns the standard (singular names, NOT NULL defaults, _at / _date suffixes, DATETIME over TIMESTAMP, adjective booleans, justified VARCHAR widths, DECIMAL money, ON DELETE by relation nature, CHECK invariants, utf8mb4_0900_*, PK width). This section only maps it onto Blueprint, where several defaults contradict it.