MySQL Design and Usage Assistant

Problem

When designing MySQL schemas and optimizing queries, common issues include inconsistent naming, poor primary-key and money-type choices, missing unique constraints, unclear VARCHAR prefix length, weak composite-index ordering, limited covering-index usage, excessive JOINs, deep-pagination pressure, charset/collation mismatches, and weak checks for sharding or migration. This skill provides concrete constraints for schema design and DDL review.

How It Works

• Activation: triggered for schema design, index optimization, sharding, data migration, or requests like “how to design xxx database”.
• Baseline constraints: prefer BIGINT UNSIGNED AUTO_INCREMENT primary keys, create_time / update_time, is_deleted soft deletes, DECIMAL(18,2) money, utf8mb4 charset, and table/column COMMENTs.
• Naming and types: use lowercase snake_case names, uk_ for unique indexes, idx_ for regular indexes, TINYINT UNSIGNED for booleans, and TINYINT UNSIGNED + COMMENT for status/enum fields.
• Indexing strategy: enforce unique indexes for unique fields, prefix-length indexes for VARCHAR, high-cardinality fields first in composite indexes, covering indexes where possible, delayed joins for deep pagination, and avoid left/full wildcard queries.
• Version loading: read references/design-spec.md, references/usage-guide.md, references/best-practices.md, references/patterns.md, and version-*.md as needed to check deprecations, new capabilities, and minor-version differences.

Boundaries

Useful for business schema review, CREATE TABLE drafts, slow-query index planning, sharding evaluation, and migration pre-checks. It provides design constraints and reference material, not final database operations decisions; tables beyond 5 million rows or 2GB require sharding evaluation.