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