# Mysql Patterns

> When to activate: MySQL, MariaDB, InnoDB, mysql, JSON column, EXPLAIN, replication, ProxySQL

- Skill: `mattakushi432/mysql-patterns` (Agent Skill)
- Install (CLI): `npx skillmds@latest add mattakushi432/mysql-patterns`
- Raw SKILL.md: https://api.skillmd.com/api/skills/mattakushi432/mysql-patterns/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: Mattakushi432 (https://skillmd.com/u/mattakushi432)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/mattakushi432/mysql-patterns

---

# MySQL 8 Patterns

## JSON Column

```sql
-- Define and query
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  attrs JSON NOT NULL
);

INSERT INTO products (attrs) VALUES ('{"color":"red","size":42}');

SELECT JSON_UNQUOTE(attrs->'$.color') AS color FROM products;
SELECT * FROM products WHERE attrs->>'$.size' > 40;

-- JSON_TABLE — shred JSON to rows
SELECT jt.*
FROM products,
  JSON_TABLE(attrs, '$' COLUMNS (
    color VARCHAR(50) PATH '$.color',
    size  INT         PATH '$.size'
  )) AS jt;

-- Index on generated column from JSON
ALTER TABLE products ADD COLUMN color VARCHAR(50)
  GENERATED ALWAYS AS (JSON_UNQUOTE(attrs->>'$.color')) VIRTUAL;
CREATE INDEX idx_color ON products (color);
```

## Window Functions

```sql
SELECT
  user_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS cumulative,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
  NTILE(4) OVER (ORDER BY amount) AS quartile
FROM orders;

-- Latest row per group (using window)
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM sessions
)
SELECT * FROM ranked WHERE rn = 1;
```

## EXPLAIN and Index Tuning

```sql
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 1 AND status = 'active';

-- Composite index order matters — leading column equality first
CREATE INDEX idx_orders ON orders (status, user_id, created_at);

-- Covering index
CREATE INDEX idx_cover ON orders (user_id, status) INCLUDE (total);

-- Force index hint
SELECT * FROM orders FORCE INDEX (idx_orders) WHERE status = 'pending';

-- Check index usage
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';
```

## InnoDB Internals

```sql
-- Check engine status
SHOW ENGINE INNODB STATUS\G

-- Buffer pool hit rate (should be > 99%)
SELECT (1 - (
  SELECT variable_value FROM performance_schema.global_status
  WHERE variable_name = 'Innodb_buffer_pool_reads'
) / (
  SELECT variable_value FROM performance_schema.global_status
  WHERE variable_name = 'Innodb_buffer_pool_read_requests'
)) * 100 AS hit_rate_pct;

-- Deadlock detection
SELECT * FROM performance_schema.data_locks;
SELECT * FROM information_schema.INNODB_TRX;
```

## Replication

```sql
-- Primary config (my.cnf)
-- server-id = 1
-- log_bin = /var/log/mysql/mysql-bin.log
-- binlog_format = ROW
-- gtid_mode = ON
-- enforce_gtid_consistency = ON

-- Replica setup
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary-host',
  SOURCE_USER='replication_user',
  SOURCE_PASSWORD='secret',
  SOURCE_AUTO_POSITION = 1;
START REPLICA;

-- Check lag
SHOW REPLICA STATUS\G
-- Seconds_Behind_Source should be near 0

-- Semi-sync for durability
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;
```

## ProxySQL Connection Pooling

```sql
-- Route reads to replicas
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, '^SELECT', 2);  -- hostgroup 2 = replicas

-- Stats
SELECT hostgroup, srv_host, status, ConnUsed, ConnFree
FROM stats.stats_mysql_connection_pool;
```

## Performance Checklist

- `innodb_buffer_pool_size` = 70-80% of RAM
- `max_connections` tuned to actual concurrent load
- Avoid `SELECT *` — fetch only needed columns
- Use `LIMIT` with `ORDER BY` on indexed column for pagination
- Batch inserts with multi-row `INSERT` or `LOAD DATA INFILE`
- Use `pt-query-digest` from Percona Toolkit to analyze slow log