What does this skill do?

The MySQL Production Patterns Skill provides proven patterns for designing, optimizing, and operating MySQL and MariaDB databases in production. It covers everything from schema design with composite indexes and the correct data types to advanced queries such as upserts, keyset pagination, and queues with SKIP LOCKED, as well as connection pool configuration and query analysis using EXPLAIN.

Layout and Table of Contents Design
Define tables with BIGINT primary keys, utf8mb4, DECIMAL for currency values, and composite indexes with the correct predicate order.
Troubleshooting Slow Queries
Analyze queries using EXPLAIN, detect risk indicators such as "type ALL" or "Using filesort," and apply the appropriate index or pattern.
Migration Review
Validate migrations before running them on large production tables, using a rollback plan and checking for locks.
Pool and Replica Configuration
Sets the pool recycling interval to less than the wait_timeout, enables pre-ping, and configures read replicas and TLS.

Usage examples

🔍 Slow query
This query takes 3 seconds to run on a table with 50 million rows. Please help me analyze it using EXPLAIN and optimize it: SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 100;
🏗️ Outline Review
Take a look at this CREATE TABLE statement for an orders table in production and let me know if the indexes and data types are correct.
📄 Keyset pagination
I need to implement pagination without OFFSET in a product table with millions of rows. Give me the keyset pattern.
🔌 Connection pool
Set up a connection pool in Node.js for MySQL with recycling and pre-ping after a replica failover.

Features

Proven schema patterns BIGINT UNSIGNED, utf8mb4, and DECIMAL primary keys for monetary values; soft deletes with scoped indexes; and composite indexes with the correct predicate order.
Optimized Queries MySQL- and MariaDB-compatible upserts, keyset pagination without OFFSET, queues with SKIP LOCKED, and full-text search.
Analysis Using EXPLAIN Detect risk indicators such as "type ALL," "key NULL," high-row estimates, or "Using filesort" before adding indexes.
Connection Pool Configuration Recycling before the `wait_timeout` expires, `pool_pre_ping` for failovers, and `connectionLimit` adjusted to the workload.
Differences Between MySQL and MariaDB It identifies syntax differences between engines and recommends the compatible or version-specific form based on the detected version.

Frequently asked questions

Yes. The skill detects the engine and version using `SELECT VERSION()` and adapts the recommendations to account for the differences in syntax between MySQL and MariaDB.
No. The skill provides ready-to-use templates and SQL code. You just need access to your MySQL or MariaDB database to run the queries.
Yes. It is specifically designed for production environments: it covers keyset pagination, migration review, lock analysis, and rollback plans.
Yes. The skill validates migrations in large tables, checks for potential locks, simulates the change, and defines rollback criteria before applying it to production.
MySQL Patterns for Production — Robust Databases with Claude AI

¿Prefieres escuchar el contenido? Genera la narración de audio con un clic.