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:

-- 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):

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

Generated Column + Index (JSON):

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):

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

Cursor Pagination:

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

Queue Processing (MySQL 8.0+):

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:

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

Anti-Pattern Detection

-- 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

-- 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

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

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 |

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

Quick reference for MySQL 5.7+ / 8.0+

mysql-patterns