MySQL Core Knowledge

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: mysql for comprehensive documentation.

Table Definition

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    INDEX idx_email (email),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Common Queries

-- Pagination
SELECT * FROM users
WHERE is_active = TRUE
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Join
SELECT u.*, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id;

-- Upsert
INSERT INTO users (email, name)
VALUES (?, ?)
ON DUPLICATE KEY UPDATE name = VALUES(name);

Key Differences from PostgreSQL

Feature	MySQL	PostgreSQL
Auto ID	`AUTO_INCREMENT`	`SERIAL`
Boolean	`TINYINT(1)`	`BOOLEAN`
Upsert	`ON DUPLICATE KEY`	`ON CONFLICT`
JSON	`JSON`	`JSONB` (indexed)

When NOT to Use This Skill

PostgreSQL-specific features - Use postgresql skill for JSONB, arrays, window functions
NoSQL operations - Use mongodb or redis skills for document/key-value stores
Oracle database - Use oracle skill for Oracle-specific features
SQL Server - Use sqlserver skill for T-SQL and SQL Server features
ORM abstractions - Use framework-specific skills (Prisma, TypeORM, Sequelize)

Anti-Patterns

Anti-Pattern	Issue	Solution
`SELECT *` in production	Transfers unnecessary data, performance impact	Specify needed columns explicitly
Missing `WHERE` on UPDATE/DELETE	Modifies all rows unintentionally	Always include WHERE clause
Missing indexes on JOIN/WHERE columns	Full table scans, slow queries	Add indexes on frequently queried columns
MyISAM for transactional data	No transaction support, table-level locking	Use InnoDB engine
`LIKE '%pattern'`	Cannot use index, full scan	Use `LIKE 'pattern%'` or fulltext search
Missing `LIMIT` on large tables	Can crash application	Always paginate results
Using `ENUM` for frequently changing values	Requires ALTER TABLE to add values	Use lookup table instead
Missing foreign keys	Data integrity issues	Define proper FK constraints
N+1 query problem	One query per row in loop	Use JOINs or batch queries
Not using prepared statements	SQL injection risk, slower performance	Use parameterized queries

Quick Troubleshooting

Problem	Diagnostic	Fix
Slow queries	`EXPLAIN SELECT ...`	Add indexes, rewrite query, analyze execution plan
High CPU usage	`SHOW PROCESSLIST` to find slow queries	Optimize top queries, add indexes
Connection limit reached	`SHOW STATUS LIKE 'Threads_connected'`	Increase max_connections, use connection pooling
Lock wait timeout	Check `SHOW ENGINE INNODB STATUS`	Reduce transaction time, optimize queries
Disk space full	`SELECT table_schema, SUM(data_length+index_length) FROM information_schema.tables GROUP BY 1`	Archive old data, optimize tables
Replication lag	`SHOW SLAVE STATUS`	Increase resources, tune binlog settings
Table corruption	`CHECK TABLE table_name`	Run `REPAIR TABLE` or restore from backup
Deadlocks	Check error log and `SHOW ENGINE INNODB STATUS`	Reduce transaction scope, access tables in same order

Reference Documentation

Indexes
JSON