MySQL Tuning skill for Codex

Fix the statements that cost the most total time, with indexes designed for InnoDB’s clustered layout, and touch server configuration only when metrics point at it. Done well means each change is justified by a digest, a plan or a status counter, and was measured before and after.

Find what to tune

Read AGENTS.md for how to reach a production-like database and whether you may run queries there. Then:

SELECT DIGEST_TEXT, COUNT_STAR AS calls,
       ROUND(SUM_TIMER_WAIT/1e12, 1) AS total_s,
       ROUND(AVG_TIMER_WAIT/1e9, 2)  AS avg_ms,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT, SUM_NO_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 15;

Timers are in picoseconds. Red flags: SUM_ROWS_EXAMINED hundreds of times SUM_ROWS_SENT (scanning to find little), SUM_NO_INDEX_USED > 0 on hot digests. The sys schema has shortcuts: sys.statements_with_full_table_scans, sys.statements_with_temp_tables, sys.statements_with_sorting.

Slow log for history and exact parameter values:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_slow_extra = ON;    -- 8.0.14+: rows examined, bytes, temp tables per entry

SET GLOBAL changes a live server: confirm with the user first, and use SET PERSIST only if they want it to survive restarts. Summarize with pt-query-digest slow.log (Percona Toolkit, may need installing).

Read the plan

EXPLAIN FORMAT=TREE SELECT ...;   -- estimated plan, readable
EXPLAIN ANALYZE SELECT ...;       -- 8.0.18+: executes the query, shows actual rows and time

EXPLAIN ANALYZE runs the statement; do not use it on writes in production.

In the plan	Meaning	Action
type: ALL / Table scan	Full scan	Index the filter, or the predicate is not sargable
type: index	Full index scan	Usually still too much; better leading columns
ref, eq_ref, range, const	Index lookups	Good; check rows estimate
Using filesort	Sort not served by an index	Composite index ending in the ORDER BY columns
Using temporary	Temp table for GROUP BY/DISTINCT/UNION	Index matching GROUP BY, or reduce rows first
Using index	Covering, no row lookup	Ideal for hot reads
Using index condition	Index condition pushdown	Fine
rows estimate far from actual rows	Bad stats	ANALYZE TABLE; histograms for skewed non-indexed columns

Index design for InnoDB

• The table is stored in primary key order, and every secondary index stores the PK columns. A wide PK (UUID strings, composite natural keys) bloats every index. Prefer BIGINT UNSIGNED AUTO_INCREMENT, or UUIDs as BINARY(16) with UUID_TO_BIN(uuid, 1) (time-ordered for v1) or application-generated v7.
• Composite order: equality columns first, then one range or the ORDER BY columns. The leftmost-prefix rule applies: (a, b, c) serves a, a,b, a,b,c, not b alone.
• Because secondary indexes include the PK, INDEX (customer_id, created_at) already covers SELECT id FROM orders WHERE customer_id = ? ORDER BY created_at.
• Functional indexes (8.0.13+) need double parentheses: CREATE INDEX users_email_lower ON users ((LOWER(email)));
• Descending indexes are real in 8.0+: INDEX (account_id, created_at DESC).
• Prefix indexes on long VARCHAR/TEXT (INDEX (url(100))) cannot cover or sort.

Removing indexes safely:

SELECT * FROM sys.schema_unused_indexes;        -- since last restart only
SELECT * FROM sys.schema_redundant_indexes;
ALTER TABLE orders ALTER INDEX orders_legacy_idx INVISIBLE;   -- optimizer ignores it, still maintained
-- watch for a week of traffic, then drop, or flip back VISIBLE instantly

Check replicas too; stats are per server.

Statistics

ANALYZE TABLE orders;                                       -- refresh index stats
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, region WITH 64 BUCKETS;
SELECT * FROM information_schema.COLUMN_STATISTICS WHERE TABLE_NAME = 'orders';

Histograms help non-indexed columns with skew used in filters and joins. They are not updated automatically; refresh after big data changes.

Query patterns that defeat indexes

Pattern	Fix
WHERE DATE(created_at) = '2026-10-01'	Range: created_at >= '2026-10-01' AND created_at < '2026-10-02'
WHERE phone = 5551234 on a VARCHAR column	Quote the value; numeric comparison converts every row
Join columns with different charsets/collations	Align column definitions; mismatches force conversion and block index use
LIKE '%term'	Full-text index (MATCH ... AGAINST) or external search
OR across columns	UNION ALL, or rely on index merge only after checking the plan
LIMIT 50 OFFSET 200000	Keyset pagination on (created_at, id)
SELECT * on wide rows when an index could cover	Select only needed columns

Optimizer hints (/*+ INDEX(orders orders_customer_created_idx) */, FORCE INDEX) are a last resort: they freeze today’s plan and break silently when the index is renamed.

Configuration review

Collect evidence first: SHOW GLOBAL STATUS, SHOW ENGINE INNODB STATUS\G, and SHOW VARIABLES. Defaults differ between 8.0 and 8.4 (8.4 turned off the adaptive hash index and changed IO capacity defaults), so read actual values.

Setting	Guidance
innodb_buffer_pool_size	50-75% of RAM on a dedicated server. Check hit rate: Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests should be well under 1%
innodb_redo_log_capacity (8.0.30+)	Size for roughly an hour of peak writes; small redo logs cause checkpoint stalls
innodb_flush_log_at_trx_commit	1 for durability; 2 trades up to ~1 s of commits on OS crash for throughput. Ask before changing
sync_binlog	1 with replication or PITR from binlogs
innodb_io_capacity	Match storage: SSD/NVMe in the thousands
max_connections	Do not raise as a fix for pool exhaustion; use a pooler and fix slow queries
innodb_dedicated_server	ON lets MySQL size buffer pool and redo itself on a dedicated host

Managed MySQL (RDS, Aurora, Cloud SQL, PlanetScale) changes settings via parameter groups or consoles; tell the user what to change rather than attempting it.

Online DDL

ALTER TABLE orders ADD COLUMN note VARCHAR(255) NULL, ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX orders_status_idx (status), ALGORITHM=INPLACE, LOCK=NONE;

• Always state ALGORITHM (and LOCK) so MySQL fails fast instead of silently copying the table.
• INSTANT supports adding/dropping columns anywhere (8.0.29+), renaming columns, and changing defaults; type changes generally need COPY.
• Every DDL needs a metadata lock. A long transaction holding the table blocks it, and all later queries queue behind it. Set SET SESSION lock_wait_timeout = 5; for DDL.
• Large tables needing COPY: use gh-ost or pt-online-schema-change ($mysql-online-schema-change).

Verification

• Target digests chosen from performance_schema or slow log totals
• EXPLAIN ANALYZE before and after, on production-like data, same parameters
• Rows examined per row sent dropped; filesort or temp table removed where intended
• Index removals done via INVISIBLE first, checked on all replicas
• Config changes backed by a status counter, applied with user approval, re-measured

Output template

## MySQL tuning: <digest or endpoint>
**Cost:** <calls, total s, avg ms, rows examined/sent>
**Plan before:** <access type, Extra, rows, time>
**Change:** <index DDL with ALGORITHM / rewrite / setting>
**Plan after:** <access type, Extra, rows, time>
**Side effects:** <write cost, index count, replica impact>