mysql-patterns

What this skill does

# MySQL Patterns

Quick reference for MySQL best practices. For detailed guidance, use the `mysql-database-reviewer` agent.

## When to Activate

- Writing SQL queries or migrations
- Designing database schemas
- Troubleshooting slow queries
- Setting up connection pooling
- Implementing multi-tenant isolation

## Quick Reference

### Index Cheat Sheet

| Query Pattern             | Index Type       | Example                               |
| ------------------------- | ---------------- | ------------------------------------- |
| `WHERE col = value`       | B-tree (default) | `CREATE INDEX idx ON t (col)`         |
| `WHERE col > value`       | B-tree           | `CREATE INDEX idx ON t (col)`         |
| `WHERE a = x AND b > y`   | Composite        | `CREATE INDEX idx ON t (a, b)`        |
| `MATCH(...) AGAINST(...)` | FULLTEXT         | `CREATE FULLTEXT INDEX ft ON t (col)` |
| Long string prefix        | Prefix           | `CREATE INDEX idx ON t (col(50))`     |
| Geographic data           | SPATIAL          | `CREATE SPATIAL INDEX idx ON t (col)` |

### Data Type Quick Reference

| Use Case      | Correct Type      | Avoid                        |
| ------------- | ----------------- | ---------------------------- |
| IDs           | `bigint unsigned` | `int`, random UUID as PK     |
| Business code | `varchar(64)`     | `char`, `text`               |
| Strings       | `varchar(n)`      | `text` for short strings     |
| Timestamps    | `datetime`        | `timestamp` (2038 problem)   |
| Money         | `decimal(10,2)`   | `float`, `double`            |
| Flags         | `tinyint`         | `varchar`, `boolean` literal |
| Status        | `tinyint`         | `enum` (hard to modify)      |
| JSON data     | `json`            | `text` for structured data   |

### Common Patterns

**Composite Index Order:**

```sql
-- Equality columns first, then range columns
CREATE INDEX idx_status_created ON t_order (status, created_at);
-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'
-- Does NOT work for: WHERE created_at > '2024-01-01' alone
```

**Prefix Index (Long Strings):**

```sql
CREATE INDEX idx_url ON t_page (url(100));
-- Index only first 100 characters
```

**Generated Column + Index (JSON):**

```sql
ALTER TABLE t_product
ADD COLUMN brand varchar(100) GENERATED ALWAYS AS (attributes->>'$.brand') STORED;
CREATE INDEX idx_brand ON t_product (brand);
```

**UPSERT (ON DUPLICATE KEY):**

```sql
INSERT INTO t_setting (user_code, `key`, `value`)
VALUES ('u123', 'theme', 'dark')
ON DUPLICATE KEY UPDATE
  `value` = VALUES(`value`),
  updated_at = NOW();
```

**Cursor Pagination:**

```sql
SELECT * FROM t_product WHERE id > ? ORDER BY id LIMIT 20;
-- O(1) vs OFFSET which is O(n)
```

**Queue Processing (MySQL 8.0+):**

```sql
START TRANSACTION;
SELECT * FROM t_job
WHERE status = 'pending'
ORDER BY created_at LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Process job...
UPDATE t_job SET status = 'processing' WHERE id = ?;
COMMIT;
```

**Batch Insert:**

```sql
INSERT INTO t_event (user_code, action) VALUES
  ('u1', 'click'),
  ('u2', 'view'),
  ('u3', 'click');
-- 1 round trip instead of 3
```

### Anti-Pattern Detection

```sql
-- Find tables without primary key
SELECT t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.key_column_usage k
  ON t.table_schema = k.table_schema
  AND t.table_name = k.table_name
  AND k.constraint_name = 'PRIMARY'
WHERE t.table_schema = DATABASE()
  AND k.constraint_name IS NULL
  AND t.table_type = 'BASE TABLE';

-- Find slow queries (performance_schema)
SELECT DIGEST_TEXT, COUNT_STAR,
  ROUND(AVG_TIMER_WAIT/1000000000, 2) as avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT > 100000000  -- > 100ms
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;

-- Find unused indexes (sys schema)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = DATABASE();

-- Find redundant indexes
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = DATABASE();

-- Check table fragmentation
SELECT table_name, data_free,
  ROUND(data_free/(data_length+index_length+data_free)*100, 2) as frag_pct
FROM information_schema.tables
WHERE table_schema = DATABASE() AND data_free > 1000000
ORDER BY data_free DESC;
```

### Configuration Template

```sql
-- Connection limits
SET GLOBAL max_connections = 200;
SET GLOBAL max_user_connections = 50;

-- Timeouts
SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 600;
SET GLOBAL max_execution_time = 30000;  -- 30s query timeout (MySQL 5.7.8+)

-- Slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- InnoDB settings (my.cnf recommended)
-- innodb_buffer_pool_size = 70% of RAM
-- innodb_log_file_size = 256M
-- innodb_flush_log_at_trx_commit = 1
```

### Table Template

```sql
CREATE TABLE `t_example` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'PK',
  `example_code` varchar(64) NOT NULL COMMENT 'Business code (UUID)',

  -- Business fields here
  `status` tinyint NOT NULL DEFAULT 1 COMMENT '0-inactive, 1-active',

  -- Audit fields (5 fields)
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
  `created_by` varchar(64) NOT NULL DEFAULT '' COMMENT 'Creator (user_code)',
  `updated_by` varchar(64) NOT NULL DEFAULT '' COMMENT 'Modifier (user_code)',
  `deleted_at` datetime DEFAULT NULL COMMENT 'Soft delete marker',

  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_example_code` (`example_code`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### EXPLAIN Analysis

```sql
EXPLAIN ANALYZE SELECT * FROM t_order WHERE user_code = 'u123';
```

| Indicator         | Problem             | Solution           |
| ----------------- | ------------------- | ------------------ |
| `type: ALL`       | Full table scan     | Add index          |
| `type: index`     | Full index scan     | Check WHERE        |
| `Using filesort`  | Sorting not indexed | Add ORDER BY index |
| `Using temporary` | Temp table          | Optimize GROUP BY  |
| High `rows`       | Poor selectivity    | Review conditions  |

## Related

- Agent: `mysql-database-reviewer` - Full database review workflow

---

_Quick reference for MySQL 5.7+ / 8.0+_