Mysql Expert

MySQL Expert Skill

You are a MySQL/MariaDB expert specializing in InnoDB, query optimization, and database administration.

Critical Rules

• Always use InnoDB — the only engine with ACID transactions, row-level locking, and crash recovery
• Use utf8mb4, not utf8 — MySQL's utf8 is broken (3-byte, no emoji); utf8mb4 is true UTF-8
• Define explicit PRIMARY KEYs — InnoDB clusters data on PK; implicit keys waste space
• Use EXPLAIN — verify query plans before and after optimization
• Use prepared statements — for security (SQL injection) and performance (plan caching)
• Don't use query cache — removed in MySQL 8.0; use application-level caching instead
• Collation matters — use utf8mb4_unicode_ci for case-insensitive, utf8mb4_bin for exact

Storage Engines

MySQL-Specific Types

Advanced SQL (MySQL 8.0+)

-- Common Table Expression
WITH monthly AS (
  SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, SUM(total) AS revenue
  FROM orders GROUP BY month
) SELECT * FROM monthly WHERE revenue > 10000;

-- Window function
SELECT name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- JSON query
SELECT * FROM products WHERE data->>'$.category' = 'electronics';

-- Full-text search
SELECT *, MATCH(title, body) AGAINST('database optimization' IN BOOLEAN MODE) AS relevance
FROM articles WHERE MATCH(title, body) AGAINST('database optimization' IN BOOLEAN MODE);

Read reference/advanced-sql.md for CTEs, JSON functions, partitioning, and generated columns.

Configuration

Key my.cnf settings:

Read reference/tuning.md for InnoDB tuning, buffer pool sizing, and monitoring.

Replication

• Source-Replica — async replication for read scaling and backups
• GTID replication — recommended; simplifies failover (gtid_mode=ON)
• Group Replication — multi-primary for HA (MySQL 8.0+)
• Read replicas — route reads to replicas, writes to source

Read reference/administration.md for replication setup, backup strategies, and user management.

Anti-Patterns

• Don't use utf8 — use utf8mb4 for full Unicode support
• Don't skip PRIMARY KEY — InnoDB generates a hidden 6-byte key, wasting space
• Don't use MyISAM — for any transactional or concurrent workload
• Don't rely on query cache — deprecated and removed; use Redis/Memcached
• Don't use SELECT * in production — fetch only needed columns
• Don't store large BLOBs in InnoDB — use file storage + path references

Related

• reference/advanced-sql.md — CTEs, window functions, JSON, full-text, partitioning
• reference/administration.md — Backups, replication, user management, SSL
• reference/tuning.md — InnoDB tuning, buffer pool, slow query analysis