skills.rest
azusagasaku
azusagasakuCOMMUNITY
Explore all skills by this author →
GitHub

mysql-patterns

Provides MySQL and MariaDB schema, indexing, transaction, replication, and connection pool patterns.

Updated May 19, 2026
Search Tags:#query optimization#indexing#schema design#MySQL#transactions#mariadb#connection pool
One-click install
npx skills add https://github.com/azusagasaku/--claude-config --skill mysql-patterns-azusagasaku
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: mysql-patterns
Source: https://github.com/azusagasaku/--claude-config/tree/main/skills/ecc/mysql-patterns
Command: npx skills add https://github.com/azusagasaku/--claude-config --skill mysql-patterns-azusagasaku

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Designing and operating MySQL or MariaDB databases in production involves subtle decisions around schema defaults, index ordering, transaction locking, upsert syntax differences between engines, and connection pool tuning. This Skill gives database administrators and backend developers concrete, version-aware SQL patterns and review checklists so they avoid common correctness, locking, and performance pitfalls. ## Core Features & Use Cases - Schema and Indexing Patterns: Default table definitions with utf8mb4, BIGINT UNSIGNED keys, DECIMAL for money, composite index ordering rules, and EXPLAIN risk signals. - Query and Transaction Patterns: Cross-engine upserts, keyset pagination, JSON generated columns, full-text search, deadlock avoidance, and SKIP LOCKED queue claims. - Operations Guidance: Connection pool configuration for SQLAlchemy and mysql2, slow query diagnostics, replication lag handling, security hardening, and InnoDB configuration starting points. - Use Case: While reviewing a migration that adds an index to a large orders table, use this Skill to verify index column order with EXPLAIN, check MySQL versus MariaDB syntax differences, and produce a validation and rollback plan. ## Quick Start Ask the AI to review your MySQL schema design or slow query using the mysql-patterns skill and return the highest-risk issues with exact SQL fixes.

Frequently Asked Questions about mysql-patterns

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I design indexes for MySQL queries with multiple filters?▼

Order composite indexes with equality predicate columns first, then range or sort columns, such as (account_id, status, created_at). Validate the index with EXPLAIN and watch for type ALL, NULL key, or Using filesort signals before deploying.

How to write an upsert that works on both MySQL and MariaDB?▼

Use INSERT ... ON DUPLICATE KEY UPDATE with VALUES(col) to reference inserted values, which MariaDB supports and MySQL still accepts. The newer row-alias form (VALUES (...) AS new) is MySQL-only, so confirm the engine with SELECT VERSION() first.

What is the difference between MySQL and MariaDB upsert syntax?▼

MySQL documents row aliases as the replacement for VALUES(col) in ON DUPLICATE KEY UPDATE and deprecates VALUES(col). MariaDB documents VALUES(col) as the supported form, making it the compatible choice for mixed fleets.

When should I use SKIP LOCKED in MySQL transactions?▼

Use FOR UPDATE SKIP LOCKED only for queue-style worker claims where skipping locked rows is acceptable. It can return an inconsistent view of the data, so never use it for accounting or integrity-sensitive reads.

Why do pooled MySQL connections go stale and how do I fix it?▼

Connections go stale when the pool recycles them slower than the server wait_timeout closes them. Set pool_recycle below wait_timeout (for example 240 seconds against a 300-second timeout) and enable pool_pre_ping to recover from network or failover events.

Can I read from a MySQL replica right after writing?▼

Replicas can lag, so read-your-own-write paths, checkout flows, permission checks, and idempotency-key reads should stay on the primary immediately after a write. Monitor replica SQL thread, IO thread, and lag with SHOW REPLICA STATUS or SHOW SLAVE STATUS depending on version.

Related Skills

Explore More Software Engineering Skills→
mirzadhammirzadham

mysql-patterns

Provides MySQL and MariaDB schema, indexing, transaction, and replication patterns for production databases.

Community
Advanced
mattbutlerengineeringmattbutlerengineering
1

prisma-migrate-safety

Validate Prisma migrations against a staging database before production deployment.

Community
Intermediate
vorynkavitaliyvorynkavitaliy

secrets-management

Centralize secret storage and automate rotation in CI/CD pipelines.

Community
Intermediate
johangm90johangm90

spex-db

Design database schemas, ERDs, and migration strategies for SQL and NoSQL databases.

Community
Advanced