mysql
π―SkillInstallation
npx skills add g1joshi/agent-skills --skill mysqlRun it in a terminal; it asks which AI coding tools to install it for (Claude Code, Codex, Cursor and more).
Uses skills.sh Β· Node.js 22.20+
After installing
Ask for related work and the agent uses it on its own, or call it yourself.
/mysqlInstall by hand
Copy the whole skill folder from GitHub to the place below. Copying only SKILL.md can leave out files the skill uses.
Open the skill folder β- This project
.claude/skills/mysql/ - All projects
~/.claude/skills/mysql/
These paths are for Claude Code. For other tools, the command above puts it in the right place.
SKILL.md
View on GitHub βExpert MySQL relational database assistance covering InnoDB engine, query optimization with EXPLAIN, transactions, and replication. Use when designing relational schemas, tuning SQL queries, or scaling MySQL.
MySQL
MySQL is a widely used relational database management system (RDBMS). It is the "M" in the LAMP stack and powers huge platforms like Facebook and WordPress.
When to Use
- Web Application Relational Backbone: The world's most popular open-source relational database powering WordPress, Rails, and web backends.
- ACID Transaction Guarantees: Processing financial transactions, order checkouts, and inventory balances reliably with InnoDB.
- Read-Heavy Web Applications: Scaling reads horizontally using primary-replica asynchronous and semi-synchronous replication topologies.
- Modern Document Store (JSON Columns): Indexing and querying semi-structured JSON documents alongside relational SQL tables.
Quick Start
-- Upsert (Insert or Update)
INSERT INTO users (id, name) VALUES (1, 'Jane')
ON DUPLICATE KEY UPDATE name = 'Jane';
Core Concepts
InnoDB Storage Engine & B+Tree Clustered Indexes
InnoDB stores table rows clustered physically on disk by primary key, providing instant primary key lookups:
CREATE TABLE commerce.orders (
order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
total_amount DECIMAL(12, 2) NOT NULL,
status ENUM('PENDING', 'PAID', 'SHIPPED', 'CANCELLED') DEFAULT 'PENDING',
details JSON,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_user_status (user_id, status)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
JSON Functional Indexes
Creates virtual indexed columns over specific JSON paths to accelerate document queries:
-- Create functional index on JSON path
ALTER TABLE commerce.orders
ADD INDEX idx_customer_tier ((CAST(details->>'$.customer.tier' AS CHAR(20))));
-- Optimized index query
SELECT * FROM commerce.orders
WHERE details->>'$.customer.tier' = 'Platinum';
Semi-Synchronous Replication Topology
Guarantees that at least one read replica has received transaction events before returning success:
[ Primary Master ] ββReplication Eventβββ [ Replica 1 (Awaits ACK) ] ββACKβββ [ Primary Commits ]
β
βΌ
[ Replica 2 (Async) ]
Common Patterns
Keyset Pagination (Seek Method)
Problem: Traditional OFFSET 1000000 LIMIT 20 scans and discards 1 million rows, resulting in extreme query latency.
Solution: Use keyset seek pagination utilizing indexed primary key comparisons:
-- Fast seek pagination regardless of page depth
SELECT id, title, price, created_at
FROM products
WHERE (created_at, id) < ('2025-03-01 10:00:00', 94821)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Best Practices
Do:
- Choose Compact Primary Keys: Use
BIGINT UNSIGNED AUTO_INCREMENTor ordered UUIDs (v7); secondary indexes store the primary key. - Size
innodb_buffer_pool_sizeAppropriately: Allocate 70-80% of total physical RAM to the InnoDB buffer pool on dedicated servers. - Use
EXPLAIN FORMAT=JSON: Inspect query execution plans to identify temporary tables, filesorts, and full table scans. - Enable Binary Logging with Row Format: Set
binlog_format = ROWfor safe, deterministic replication and point-in-time recovery.
Don't:
- Use random UUIDv4 as primary keys: Random UUIDs cause index page fragmentation and severe disk I/O thrashing during inserts.
- Use
SELECT *in production code: Query only needed columns to leverage covering indexes. - Store unstructured text without character sets: Default to
utf8mb4withutf8mb4_0900_ai_cicollation for full Unicode support.
Troubleshooting
| Error | Cause | Solution |
|---|---|---|
ERROR 1205 (HY000): Lock wait timeout exceeded | Long-running transaction holding row locks requested by another query. | Kill blocking transaction (SHOW PROCESSLIST) and keep transactions short. |
ERROR 1040: Too many connections | Connection pool exhaustion exceeding max_connections. | Increase max_connections or place ProxySQL connection pooler in front. |
Using filesort; Using temporary in EXPLAIN | Query cannot use index to satisfy ORDER BY or GROUP BY. | Add composite index that includes both WHERE filter columns and ORDER BY columns in order. |
References
More from this repository10
A template skill providing a starter framework for creating new agent skills with structured sections for triggers, quick start examples, and capability descriptions.
Skill
Skill
Skill
Skill
Skill
Skill
Skill
Skill
Skill