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