mysql-best-practices
MySQL development best practices for schema design, query optimization, and database administration
Security Assessment
About mysql-best-practices
The MySQL Best Practices skill provides comprehensive guidance for developing robust, performant, and maintainable MySQL database systems. It addresses common challenges in schema design, query optimization, and database administration by offering proven patterns and techniques specific to MySQL's architecture and capabilities. This skill helps developers avoid common pitfalls like inefficient data types, poor indexing strategies, and suboptimal storage engine choices that can lead to performance bottlenecks and maintenance issues.
The skill covers critical areas including storage engine selection (InnoDB, MyISAM, MEMORY), appropriate data type usage for minimizing storage and improving performance, primary key design patterns, and comprehensive indexing strategies. It provides practical SQL examples demonstrating proper table creation with appropriate constraints, charset configuration (utf8mb4 for full Unicode support), and index types including B-tree, FULLTEXT, SPATIAL, and covering indexes. The skill also includes guidance on connection pooling, query caching, and MySQL-specific security hardening practices.
This skill is ideal for backend developers working with MySQL databases, database administrators responsible for schema design and optimization, and development teams building data-intensive applications. Whether you're designing a new database schema, optimizing existing queries, or troubleshooting performance issues, this skill provides actionable best practices grounded in MySQL's specific implementation details and optimization characteristics.
FAQ
When should I use InnoDB versus MyISAM storage engine?
Use InnoDB as the default engine for most use cases because it provides ACID compliance and row-level locking, which are essential for transactional workloads. Only consider MyISAM for read-heavy, non-transactional workloads where table-level locking is acceptable.
What charset should I use for MySQL tables?
Always use utf8mb4 charset with utf8mb4_unicode_ci collation for full Unicode support. This ensures proper storage of emojis and other 4-byte Unicode characters that the older utf8 charset cannot handle.
How should I choose between DECIMAL and FLOAT for numeric data?
Use DECIMAL for financial calculations and any scenario where precision is critical, as FLOAT and DOUBLE are approximate numeric types that can introduce rounding errors. DECIMAL stores exact numeric values.
What's the best way to store UUIDs in MySQL?
Store UUIDs as BINARY(16) rather than VARCHAR(36) to save space and improve performance. Use UUID_TO_BIN() when inserting and BIN_TO_UUID() when retrieving to convert between formats.
How do I create effective composite indexes?
Place the most selective columns first in composite indexes and order them based on your query patterns. Consider creating covering indexes that include all columns needed by frequently executed queries to avoid table lookups.
Install mysql-best-practices
Quick Setup:
- Copy the skill folder to
.claude/skills/ - Claude will automatically detect and use the skill
Repository
mindrally/skills