MySQL Best Practices

MySQL Best Practices

Comprehensive performance optimization guide for MySQL, adapted from PostgreSQL best practices. Contains rules across 8 categories, prioritized by impact to guide automated query optimization and schema design.

When to Apply

Reference these guidelines when:

• Writing SQL queries or designing schemas
• Implementing indexes or query optimization
• Reviewing database performance issues
• Configuring connection pooling or scaling
• Optimizing for MySQL-specific features
• Working with MySQL security features

Rule Categories by Priority

How to Use

Read individual rule files for detailed explanations and SQL examples:

references/​query-missing-indexes.md
references/​schema-partial-indexes.md
references/​_sections.md

Each rule file contains:

• Brief explanation of why it matters
• Incorrect SQL example with explanation
• Correct SQL example with explanation
• Optional EXPLAIN output or metrics
• Additional context and references
• MySQL-specific notes

Full Compiled Document

For the complete guide with all rules expanded: AGENTS.md

MySQL vs PostgreSQL Key Differences

This skill adapts PostgreSQL best practices to MySQL. Key differences:

• JSON: MySQL uses JSON type (not JSONB), with JSON_EXTRACT(), JSON_CONTAINS() functions
• Arrays: MySQL doesn't have native arrays; use JSON arrays or normalized tables
• Full-Text Search: MySQL uses FULLTEXT indexes with MATCH() AGAINST() syntax
• UUIDs: Use CHAR(36) or BINARY(16) instead of native UUID type
• String Matching: Use LIKE (case-sensitive) or LIKE BINARY instead of ILIKE
• Type Casting: Use CAST() function instead of :: operator
• Window Functions: MySQL 8.0+ supports window functions (similar to PostgreSQL)

References

• https://dev.mysql.com/doc/
• https://dev.mysql.com/doc/refman/8.0/en/optimization.html
• https://dev.mysql.com/doc/refman/8.0/en/explain.html
• https://dev.mysql.com/doc/refman/8.0/en/json-functions.html
• https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html