home / skills / jcastillotx / vibe-skeleton-app / mysql-best-practices

mysql-best-practices skill

This skill helps you apply MySQL best practices for query optimization, security, and schema design to build robust, scalable databases.

npx playbooks add skill jcastillotx/vibe-skeleton-app --skill mysql-best-practices

Review the files below or copy the command above to add this skill to your agents.

Files (8)

SKILL.md

1.4 KB

---
name: mysql-best-practices
description: MySQL database development standards. Triggers when working with MySQL databases, queries, schema design, or optimization.
trigger_patterns:
  - mysql
  - SELECT
  - INSERT
  - UPDATE
  - CREATE TABLE
  - ALTER TABLE
  - INDEX
auto_load_with: []
---

# MySQL Best Practices

Comprehensive coding standards for MySQL database development, optimized for AI agents and LLMs.

## Overview

This skill provides 24 rules organized across 8 categories:

1. **Query Optimization (query-)** - EXPLAIN, N+1 prevention, pagination [CRITICAL]
2. **Security (security-)** - Prepared statements, least privilege, encryption [CRITICAL]
3. **Schema Design (schema-)** - Data types, normalization, constraints [HIGH]
4. **Indexing Strategy (index-)** - Composite indexes, covering indexes [HIGH]
5. **Transaction Management (txn-)** - ACID, isolation levels, deadlocks [MEDIUM-HIGH]
6. **Connection Management (conn-)** - Pooling, timeouts [MEDIUM]
7. **Backup & Recovery (backup-)** - Strategies, point-in-time recovery [MEDIUM]
8. **Replication (repl-)** - Master-slave, read replicas [LOW-MEDIUM]

## Usage

Reference this skill when:
- Designing database schemas
- Writing or optimizing SQL queries
- Implementing indexes
- Managing transactions
- Configuring connections

## Build

```bash
pnpm build    # Compile rules to AGENTS.md
pnpm validate # Validate rule files
```

Overview

This skill codifies MySQL database development standards into clear, actionable rules for schema design, queries, indexing, transactions, security, and operations. It helps developers and AI agents follow proven practices that improve performance, reliability, and security. The guidance is organized into categories so you can apply the right rule at the right stage of development.

How this skill works

The skill inspects MySQL-related tasks and suggests specific standards and checks based on context: query patterns, schema definitions, index usage, transaction handling, connection settings, backup strategies, and replication setup. It flags common pitfalls (N+1 queries, missing indexes, unsafe concatenation), recommends fixes (prepared statements, composite indexes, EXPLAIN-driven optimization), and provides operational advice for backups and replication. Recommendations are concise and actionable for immediate implementation.

When to use it

Designing or reviewing database schemas and migration plans
Writing, profiling, or optimizing SQL queries and ORMs
Implementing or tuning indexes and query plans
Configuring transactions, isolation levels, and deadlock handling
Setting up connection pools, timeouts, and production deployments

Best practices

Always use prepared statements or parameterized queries to prevent injection and improve plan reuse
Run EXPLAIN on slow queries; eliminate full table scans with appropriate indexes and covering indexes
Avoid N+1 query patterns by batching or joining related data; use pagination strategies with indexed cursors
Choose appropriate data types and normalization level; enforce constraints and NOT NULL where possible
Manage transactions explicitly: keep them short, handle retries for deadlocks, and set the right isolation level
Use connection pooling, sensible timeouts, least-privilege accounts, and encrypt data in transit and at rest

Example use cases

Reviewing an ORM-generated query set to detect N+1 problems and suggest batched loading
Optimizing slow report queries by recommending composite and covering indexes after EXPLAIN analysis
Hardening an application by converting string-concatenated SQL to prepared statements and restricting DB user privileges
Designing a schema for a multi-tenant app with proper data types, foreign keys, and partitioning suggestions
Advising backup and recovery configuration: full/differential schedules and point-in-time recovery setup

FAQ

How do I choose between normalization and denormalization?

Normalize to reduce redundancy and ensure data integrity; denormalize selectively to speed read-heavy queries, but document trade-offs and add constraints or background jobs to maintain consistency.

When should I add a composite index?

Add a composite index when queries filter or sort on multiple columns together; order the columns in the index to match the most selective and most-used query patterns.