Search the docs, videos, blog, apps, teams, skills and release notes.
At a glance
What it is
MySQL optimization expertise covering query optimization with EXPLAIN analysis, indexing strategies, InnoDB tuning, slow query log analysis, connection pooling, replication architectures (master-slave, group replication), partitioning, character set handling, and backup strategies using mysqldump and Percona XtraBackup.
A skill is a written procedure a bot loads when a job calls for it. This one is a single file, SKILL.md, and the whole file is on this page.
The whole file, exactly as it downloads. Read it before you rely on it.
SKILL.md
443 lines · 1,864 words
MySQL optimization is a systematic discipline. Every performance issue has a root cause discoverable through methodical analysis. The optimization workflow is: measure, analyze with EXPLAIN, identify the bottleneck, fix it, and verify the improvement. Never optimize without evidence.
MySQL optimization expertise covering query optimization with EXPLAIN analysis, indexing strategies, InnoDB tuning, slow query log analysis, connection pooling, replication architectures (master-slave, group replication), partitioning, character set handling, and backup strategies using mysqldump and Percona XtraBackup.
Nothing. It ships with Murage, which is open source. Your bots run on the AI plan you already have.
Yes. Open it in Settings → Skills to read, edit or duplicate it. Skill Guard scans it again after every edit.
Download the app, connect the AI you already pay for, and tell your Chief of Staff what needs doing. Plan on about ten minutes from install to a working team.
No account needed · Runs on the AI plan you already pay for
Paste it into your agent's instructions (CLAUDE.md, AGENTS.md or a custom GPT), or add it to a bot in Murage.
Ask Ember for the bot, then switch the skill on in its settings.
Step 1
Send your Chief of Staff this message. It proposes the bot and its role first, and creates it when you say go.
Send this to Ember
“Create a bot that uses the MySQL Optimizer skill, and show me the plan first.”
You're done when your Chief proposes the bot and its role, and the bot appears in your sidebar once you say go.
Step 2
You're done when you see the bot’s skills and the library.
Step 3
You're done when the skill shows as on for that bot.
Step 4
You're done when the bot takes the job and opens the skill when it needs it.
Prefer to do it yourself? Choose + in the sidebar, then New Bot.Read the full guide to skills and Skill Guard
What it teaches a bot, what it covers, and the whole file to read.
What it covers
Core Philosophy
Query Optimization with EXPLAIN
Indexing Strategy
InnoDB Tuning
Slow Query Log Analysis
Connection Pooling
Replication
Partitioning
Character Set Handling
Backup Strategies
When to Use
Output Format
Example
Edge Cases
EXPLAIN FORMAT=TREE
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
AND o.created_at > '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 20;| Column | What to Look For |
|---|---|
type | ALL (full scan) is bad. Best to worst: system > const > eq_ref > ref > range > index > ALL |
key | Which index is used. NULL means no index |
rows | Estimated rows examined. High numbers indicate missing indexes |
filtered | Percentage of rows remaining after table condition. Low values mean scanning lots of unused rows |
Extra | Using filesort (expensive sort), Using temporary (temp table), Using index (covering index, good) |
EXPLAIN ANALYZE
SELECT c.name, COUNT(*) as order_count, SUM(o.total) as total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.created_at >= '2025-01-01'
GROUP BY c.id
HAVING total_spent > 1000
ORDER BY total_spent DESC
LIMIT 10;
-- Shows actual execution times and row counts vs estimates
-- -> Limit: 10 row(s) (actual time=45.3..45.3 rows=10 loops=1)
-- -> Sort: total_spent DESC, limit input to 10 row(s)
-- -> Filter: (total_spent > 1000)
-- -> Table scan on <temporary>-- Problem 1: Full table scan (type=ALL)
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2025;
-- Fix: Avoid functions on indexed columns
SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';
-- Problem 2: Using filesort on large result set
EXPLAIN SELECT * FROM orders WHERE customer_id = 123 ORDER BY created_at DESC;
-- Fix: Composite index matching WHERE + ORDER BY
CREATE INDEX idx_customer_date ON orders (customer_id, created_at DESC);
-- Problem 3: Using temporary (GROUP BY / DISTINCT)
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Fix: Index on the GROUP BY column
CREATE INDEX idx_status ON orders (status);
-- Problem 4: Implicit type conversion
EXPLAIN SELECT * FROM users WHERE phone = 5551234567; -- phone is VARCHAR
-- Fix: Use correct type
SELECT * FROM users WHERE phone = '5551234567';-- B-tree index (default, general purpose)
CREATE INDEX idx_email ON users (email);
-- Composite index (column order follows query patterns)
CREATE INDEX idx_composite ON orders (customer_id, status, created_at);
-- Supports queries filtering on:
-- (customer_id), (customer_id, status), (customer_id, status, created_at)
-- Does NOT support: (status), (status, created_at), (created_at)
-- Covering index (all query columns in index)
CREATE INDEX idx_covering ON orders (customer_id, status, created_at, total);
-- Query uses "Using index" (index-only scan, no table lookup)
SELECT status, created_at, total FROM orders WHERE customer_id = 123;
-- Prefix index (for long text columns)
CREATE INDEX idx_url ON pages (url(100));
-- Full-text index
CREATE FULLTEXT INDEX idx_content ON articles (title, body);
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('mysql optimization' IN BOOLEAN MODE);
-- Spatial index
CREATE SPATIAL INDEX idx_location ON stores (location);
-- Descending index (MySQL 8.0+)
CREATE INDEX idx_recent ON events (created_at DESC);
-- Invisible index (test impact without dropping)
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
-- Monitor performance, then:
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;
-- Or drop if not needed1. Start with queries from the slow query log
2. Identify WHERE, JOIN, ORDER BY, and GROUP BY columns
3. Build composite indexes following the order:
- Equality conditions first (WHERE status = 'active')
- Range conditions next (WHERE created_at > '2025-01-01')
- ORDER BY / GROUP BY columns last
4. Include SELECT columns for covering index when feasible
5. Avoid over-indexing: each index slows writes
6. Use FORCE INDEX only as last resort-- Check index usage (MySQL 8.0+ with sys schema)
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';
-- Check duplicate/redundant indexes
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'mydb';
-- Index statistics
SHOW INDEX FROM orders;
ANALYZE TABLE orders; -- Update index statistics
-- Index size
SELECT table_name, index_name,
ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'mydb' AND stat_name = 'size';# my.cnf / my.ini
[mysqld]
# Buffer pool: 70-80% of available RAM on dedicated server
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8 # 1 per GB, max 64
# Redo log: larger = better write performance, longer recovery
innodb_redo_log_capacity = 4G # MySQL 8.0.30+
# I/O configuration
innodb_io_capacity = 2000 # IOPS for background tasks (SSD: 2000-10000)
innodb_io_capacity_max = 4000
innodb_flush_method = O_DIRECT # Linux: bypass OS cache
innodb_flush_neighbors = 0 # Disable for SSD
# Concurrency
innodb_thread_concurrency = 0 # Let InnoDB manage
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# Doublewrite buffer
innodb_doublewrite = ON # Data safety
# Change buffer
innodb_change_buffer_max_size = 25 # Percentage of buffer pool
# Flush behavior
innodb_flush_log_at_trx_commit = 1 # 1=ACID safest, 2=per second, 0=OS decides
sync_binlog = 1 # Sync binlog on each commit-- Buffer pool utilization
SELECT
ROUND(@@innodb_buffer_pool_size / 1024 / 1024 / 1024, 2) AS pool_size_gb,
ROUND(data_length / 1024 / 1024 / 1024, 2) AS data_size_gb
FROM (
SELECT SUM(data_length + index_length) AS data_length
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
) t;
-- Buffer pool hit rate (should be > 99%)
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Hit rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)[mysqld]
slow_query_log = ON
slow_query_log_file = [system-path]
long_query_time = 0.5 # Log queries taking > 500ms
log_queries_not_using_indexes = ON
min_examined_row_limit = 1000 # Only log if examining > 1000 rows
log_slow_admin_statements = ON
log_slow_replica_statements = ON# Percona Toolkit: aggregate and rank slow queries
pt-query-digest [system-path] --limit=20
# Filter by database
pt-query-digest [system-path] --filter '$event->{db} eq "mydb"'
# Filter by time range
pt-query-digest [system-path] --since '2025-03-01' --until '2025-03-02'-- Top queries by total execution time
SELECT
DIGEST_TEXT AS query_pattern,
COUNT_STAR AS executions,
ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_seconds,
ROUND(AVG_TIMER_WAIT / 1e12, 4) AS avg_seconds,
SUM_ROWS_EXAMINED,
SUM_ROWS_SENT,
FIRST_SEEN, LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;-- Add MySQL backend servers
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES
(10, 'mysql-primary', 3306, 100),
(20, 'mysql-replica-1', 3306, 50),
(20, 'mysql-replica-2', 3306, 50);
-- Query rules: route reads to replicas
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup) VALUES
(1, 1, '^SELECT.*FOR UPDATE', 10),
(2, 1, '^SELECT', 20),
(3, 1, '.*', 10);
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;-- On primary
CREATE USER 'repl'@'%' IDENTIFIED BY 'YOUR_SECURE_PASSWORD_HERE'; -- CHANGE THIS PASSWORD
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-- On replica
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='primary-host',
SOURCE_USER='repl',
SOURCE_PASSWORD='YOUR_SECURE_PASSWORD_HERE', -- CHANGE THIS PASSWORD
SOURCE_AUTO_POSITION=1;
START REPLICA;
-- Monitor replication
SHOW REPLICA STATUS\G-- Bootstrap first node
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
-- Join additional nodes
START GROUP_REPLICATION;-- Range partitioning by date
CREATE TABLE events (
id BIGINT AUTO_INCREMENT,
event_type VARCHAR(50),
payload JSON,
created_at DATETIME NOT NULL,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p2025_01 VALUES LESS THAN (TO_DAYS('2025-02-01')),
PARTITION p2025_02 VALUES LESS THAN (TO_DAYS('2025-03-01')),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- Partition management
ALTER TABLE events ADD PARTITION (
PARTITION p2025_04 VALUES LESS THAN (TO_DAYS('2025-05-01'))
);
ALTER TABLE events DROP PARTITION p2024_01;-- Use utf8mb4 everywhere (full Unicode including emoji)
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Convert existing table
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Collation comparison:
-- utf8mb4_unicode_ci: Standard Unicode (case-insensitive)
-- utf8mb4_0900_ai_ci: MySQL 8.0 default, accent-insensitive
-- utf8mb4_bin: Binary (case-sensitive, exact match)mysqldump --single-transaction --routines --triggers --events \
--set-gtid-purged=ON --source-data=2 \
--databases mydb > backup_$(date +%Y%m%d).sql# Full backup (online, non-blocking for InnoDB)
xtrabackup --backup --target-dir=/backups/full/$(date +%Y%m%d) \
--user=root --password=pass
# Incremental backup
xtrabackup --backup --target-dir=/backups/inc/$(date +%Y%m%d) \
--incremental-basedir=/backups/full/20250301
# Prepare and restore
xtrabackup --prepare --target-dir=/backups/full/20250301
xtrabackup --copy-back --target-dir=/backups/full/20250301| Method | Speed | Size | Locking | Granularity |
|---|---|---|---|---|
| mysqldump | Slow | Small | No (InnoDB) | Database/Table |
| mydumper | Medium | Small | Minimal | Database/Table |
| XtraBackup | Fast | Large | No | Full/Incremental |
Use this skill when:
Designing or implementing mysql optimizer solutions
Reviewing or improving existing mysql optimizer approaches
Making architectural or implementation decisions about mysql optimizer
Learning mysql optimizer patterns and best practices
Troubleshooting mysql optimizer-related issues
Do NOT use this skill when:
The question is about a fundamentally different technology domain
A more specific sibling skill covers the exact topic needed
The user needs a complete hands-on tutorial rather than expert guidance
# Mysql Optimizer Analysis
## Context Assessment
[Situation summary and constraints]
## Recommended Approach
[Primary recommendation with rationale]
## Implementation Steps
1. [Step with specific details]
2. [Step with specific details]
3. [Step with specific details]
## Trade-offs and Considerations
- [Key trade-off 1]
- [Key trade-off 2]
## Next Steps
- [Immediate action item]
- [Follow-up action item]Input: "Help me implement mysql optimizer for a medium-scale production application"
Output: A structured analysis covering current state assessment, recommended mysql optimizer approach with specific patterns, implementation roadmap with milestones, and risk mitigation strategies tailored to the application scale and constraints.
Legacy system integration: When mysql optimizer must coexist with legacy approaches, provide a gradual migration path rather than a complete rewrite
Scale mismatch: When the solution complexity exceeds the project scale, recommend a simpler approach and note when to revisit
Team skill gaps: When the team lacks experience with the recommended approach, include learning resources and simpler alternatives
Conflicting requirements: When constraints conflict (e.g., performance vs. maintainability), explicitly state the trade-off and recommend based on stated priorities
SKILL.md
443 lines · 1,864 words
The text of SKILL.md is shown as it downloads, under the Apache-2.0 license, by Ferrox Labs. The license is the one in the file's own frontmatter.
Your new bot
MySQL Optimizer bot
Waiting in your sidebar once you say go.
No account needed · Runs on the AI plan you already pay for