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
