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

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

Quick reference for MySQL 5.7+ / 8.0+

mysql-patterns