Mysql Patterns
MySQL and MariaDB schema, query, indexing, transaction, replication, and connection-pool patterns for production backends. Use when designing MySQL or MariaDB schemas and indexes, or when a query, transaction, or replica lags.
Get the complete skill folder, including its scripts and reference files.
When to use this skill
Use Mysql Patterns when an AI agent needs a reusable SKILL.md workflow for this job: MySQL and MariaDB schema, query, indexing, transaction, replication, and connection-pool patterns for production backends. Use when designing MySQL or MariaDB schemas and indexes, or when a query, transaction, or replica lags.
When not to use it
Skip Mysql Patterns when the task is outside the coding category, or when a more specific skill in this directory already covers the same workflow with clearer triggers.
How to install
Where will you use it?
mkdir -p ~/.claude/skills/mysql-patterns # Save SKILL.md to ~/.claude/skills/mysql-patterns/SKILL.md
- Claude Code personal install: place the skill and its bundled files at ~/.claude/skills/mysql-patterns/SKILL.md. For a shared project, commit the folder at .claude/skills/mysql-patterns/ instead.
- Current Claude Code detects SKILL.md changes in watched directories during the session. If you just created a top-level skills directory, run /reload-skills. Check that the skill is listed before testing its trigger.
- For Claude chat or Cowork in the Desktop app, manage uploaded skills in Customize > Skills; copying files into a local Claude Code folder is not a Desktop install. Other agents may use different install locations.
Full install guide for Claude, Cursor, and Codex →
Try a starter prompt
After installing the skill, paste this starter prompt into your agent to verify it triggers on a small task.
Use the Mysql Patterns skill for this task: Use Mysql Patterns when this documented workflow matches the task. Confirm any required inputs or files before making changes.
What supports this recommendation?
71/100 provisional content score. This measures documentation, practical detail and source signals. It is not a measured task success rate or a security certification.
- Documentation
- 77/100
- Practical detail
- 68/100
- Source evidence
- 63/100
- Source signals
- 72/100
Model compatibility evidence
- claudeuntested
No model-specific signal or recorded compatibility test was found.
- chatgptuntested
No model-specific signal or recorded compatibility test was found.
- geminiuntested
No model-specific signal or recorded compatibility test was found.
- copilotuntested
No model-specific signal or recorded compatibility test was found.
- llamauntested
No model-specific signal or recorded compatibility test was found.
- perplexityuntested
No model-specific signal or recorded compatibility test was found.
- mistraluntested
No model-specific signal or recorded compatibility test was found.
- grokuntested
No model-specific signal or recorded compatibility test was found.
Inferred compatibility is based on content signals. Check the app, its available tools and setup requirements before running a skill.
What this skill does
# MySQL Patterns
Use this skill when working on MySQL or MariaDB schema design, migrations, slow-query investigation, queue-style transactions, connection pools, or production database configuration. Prefer exact version checks before applying a feature-specific pattern because MySQL and MariaDB have diverged in several SQL details.
## Activation
- Designing MySQL or MariaDB tables, indexes, and constraints - Reviewing migrations before they run on large production tables - Debugging slow queries, lock waits, deadlocks, or connection exhaustion - Adding keyset pagination, upserts, full-text search, JSON columns, or queues - Configuring application connection pools, read replicas, TLS, or slow logs
## Version Check
Start by identifying the engine and version:
```sql SELECT VERSION(); SHOW VARIABLES LIKE 'version_comment'; ```
Keep MySQL and MariaDB guidance separate when syntax differs:
- MySQL documents row aliases as the replacement for `VALUES(col)` in `ON DUPLICATE KEY UPDATE`; `VALUES(col)` is deprecated there. - MariaDB documents `VALUES(col)` as the supported way to reference inserted values in `ON DUPLICATE KEY UPDATE`; use it for cross-engine compatibility. - `SKIP LOCKED` is appropriate for queue-like work only. It skips locked rows and can return an inconsistent view, so do not use it for general accounting or integrity-sensitive reads.
## Schema Defaults
```sql CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, account_id BIGINT UNSIGNED NOT NULL, status VARCHAR(32) NOT NULL, total DECIMAL(15, 2) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), KEY idx_orders_account_status_created (account_id, status, created_at), KEY idx_orders_active (account_id, deleted_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ```
Default choices:
| Use Case | Prefer | Avoid | | --- | --- | --- | | Surrogate primary keys | `BIGINT UNSIGNED AUTO_INCREMENT` | `INT` for tables that can grow beyond 2B rows | | UUID lookup keys | `BINARY(16)` with conversion helpers | `VARCHAR(36)` primary keys on hot tables | | Money and exact quantities | `DECIMAL(p, s)` | `FLOAT` or `DOUBLE` | | User-facing text | `utf8mb4` tables and indexes | MySQL `utf8` / `utf8mb3` defaults | | Application timestamps | `DATETIME` with UTC managed by the app | Assuming `DATETIME` stores time zone metadata | | Soft deletes | `deleted_at DATETIME NULL` plus scoped indexes | Filtering soft-deleted rows without an index | | Extensible status values | lookup table or constrained `VARCHAR` | `ENUM` when values change often |
## Indexing
Composite index order usually follows equality predicates first, then range or sort columns:
```sql CREATE INDEX idx_orders_account_status_created ON orders (account_id, status, created_at);
SELECT id, total FROM orders WHERE account_id = ? AND status = 'pending' AND created_at >= ? ORDER BY created_at DESC LIMIT 50; ```
Use `EXPLAIN` before adding or changing an index:
```sql EXPLAIN SELECT id, total FROM orders WHERE account_id = 123 AND status = 'pending' ORDER BY created_at DESC LIMIT 50; ```
Signals to investigate:
| Field | Risk Signal | | --- | --- | | `type` | `ALL` on a large table | | `key` | `NULL` when a selective predicate exists | | `rows` | Very high row estimate for an interactive path | | `Extra` | `Using temporary`, `Using filesort`, or broad `Using where` |
Avoid adding indexes blindly. Each index increases write cost, migration time, backup size, and buffer-pool pressure.
## Query Patterns
### Upsert
Cross-engine-compatible form:
```sql INSERT INTO user_settings (user_id, setting_key, setting_value) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value), updated_at = CURRENT_TIMESTAMP; ```
MySQL row-alias form:
```sql INSERT INTO user_settings (user_id, setting_key, setting_value) VALUES (?, ?, ?) AS new ON DUPLICATE KEY UPDATE setting_value = new.setting_value, updated_at = CURRENT_TIMESTAMP; ```
Use the row-alias form only after confirming the target is MySQL. Use `VALUES(col)` for MariaDB or mixed MySQL/MariaDB fleets.
### Keyset Pagination
```sql SELECT id, name, created_at FROM products WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 50; ```
Back it with an index that matches the cursor:
```sql CREATE INDEX idx_products_created_id ON products (created_at, id); ```
Do not use deep `OFFSET` pagination on large tables; it makes the server scan and discard rows before returning the page.
### JSON Fields
Use JSON columns for extension data, not for fields that need heavy relational filtering or constraints.
```sql CREATE TABLE events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, payload JSON NOT NULL, event_type VARCHAR(64) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.type'))) STORED, KEY idx_events_type (event_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ```
For frequently queried JSON paths, expose a generated column and index that column. Keep foreign keys, ownership, tenancy, and lifecycle fields relational.
### Full-Text Search
```sql ALTER TABLE articles ADD FULLTEXT KEY ft_articles_title_body (title, body);
SELECT id, title, MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE) AS score FROM articles WHERE MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE) ORDER BY score DESC LIMIT 20; ```
Use external search when you need typo tolerance, complex ranking, cross-table facets, or language-specific analysis beyond built-in full-text behavior.
## Transactions
Keep transactions short and lock rows in a consistent order:
```sql START TRANSACTION;
SELECT id, balance FROM accounts WHERE id IN (?, ?) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - ? WHERE id = ?; UPDATE accounts SET balance = balance + ? WHERE id = ?;
COMMIT; ```
Deadlock and lock-wait checklist:
- Lock rows in a deterministic order across code paths. - Do external API calls before opening the transaction, not inside it. - Add indexes for predicates used in `UPDATE`, `DELETE`, and locking reads. - On deadlock, roll back and retry the whole transaction with a bounded retry budget. - Capture `SHOW ENGINE INNODB STATUS\G` soon after a deadlock; it is overwritten by later events.
Queue-style worker claim:
```sql START TRANSACTION;
SELECT id FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED;
UPDATE jobs SET status = 'processing', started_at = CURRENT_TIMESTAMP WHERE id = ?;
COMMIT; ```
Use `SKIP LOCKED` only for queue-like workloads where skipping a locked row is acceptable. It is not a replacement for normal transactional consistency.
## Connection Pools
SQLAlchemy example:
```python from sqlalchemy import create_engine
engine = create_engine( "mysql+mysqlconnector://app:[email protected]/app", pool_size=10, max_overflow=5, pool_timeout=30, pool_recycle=240, pool_pre_ping=True, connect_args={"connect_timeout": 5}, ) ```
Node.js `mysql2` example:
```javascript import mysql from 'mysql2/promise';
const pool = mysql.createPool({ host: process.env.DB_HOST, user: process.env.DB_USER, password: process.env.DB_PASSWORD, database: process.env.DB_NAME, waitForConnections: true, connectionLimit: 10, queueLimit: 0, enableKeepAlive: true, keepAliveInitialDelay: 30000, });
const [rows] = await pool.execute( 'SELECT id, total FROM orders WHERE account_id = ? LIMIT 50', [accountId], ); ```
Keep application pool recycling below the server `wait_timeout`. If the server uses `wait_timeout = 300`, a `pool_recycle` around 240 seconds is coherent; `pool_pre_ping` still helps recover from network and failover events.
## Diagnostics
Useful first-pass commands:
```sql SHOW FULL PROCESSLIST; SHOW ENGINE INNODB STATUS\G; SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'long_query_time'; ```
Enable the slow log in a controlled environment:
```sql SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = 'ON'; ```
Use `EXPLAIN ANALYZE` only when it is safe to execute the query. It runs the statement and can be expensive on production-sized data.
## Replication
Read replicas can lag. Do not route read-your-own-write paths, checkout flows, permission checks, or idempotency-key reads to a replica immediately after a write.
```sql -- MySQL legacy terminology, still common in existing fleets SHOW SLAVE STATUS\G;
-- Newer terminology where supported SHOW REPLICA STATUS\G; ```
Check the engine/version before standardizing on one command. Monitor replica SQL thread health, IO thread health, and lag, not just whether the TCP connection is alive.
## Security
```sql CREATE USER 'app'@'%' IDENTIFIED BY 'use-a-secret-manager'; GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'%';
ALTER USER 'app'@'%' REQUIRE SSL;
SELECT user, host FROM mysql.user WHERE user = '';
DROP USER IF EXISTS ''@'localhost'; DROP USER IF EXISTS ''@'%'; ```
Security review points:
- Do not grant `ALL PRIVILEGES` or `*.*` to application users. - Require TLS for application users when traffic crosses hosts or networks. - Store credentials in the platform secret manager, not in examples, scripts, or repository files. - Separate migration/admin users from runtime application users. - Audit public network exposure and bind addresses before tuning performance.
## Configuration
Example starting point for a dedicated database host:
```ini [mysqld] innodb_buffer_pool_size = 4G innodb_flush_log_at_trx_commit = 1 sync_binlog = 1
max_connections = 300 thread_cache_size = 50
wait_timeout = 300 interactive_timeout = 300 innodb_lock_wait_timeout = 10
slow_query_log = ON long_query_time = 1 log_queries_not_using_indexes = ON
log_bin = mysql-bin binlog_format = ROW binlog_expire_logs_seconds = 604800 ```
Treat configuration values as a prompt for review, not a universal preset. Size memory, connections, log retention, and durability settings from workload, hardware, backup policy, and recovery objectives.
## Anti-Patterns
| Anti-Pattern | Risk | Better Pattern | | --- | --- | --- | | `SELECT *` in hot paths | Over-fetching and brittle clients | Select explicit columns | | Deep `OFFSET` pagination | Linear scans and slow pages | Keyset pagination | | No index on foreign-key joins | Slow joins and lock-heavy deletes | Index FK columns intentionally | | Long transactions | Lock waits and large undo history | Commit small units of work | | Direct DML against `mysql.user` | Grant-table corruption risk | Use `CREATE USER`, `ALTER USER`, `DROP USER` | | Application user with admin grants | High blast radius | Least-privilege runtime user | | Pool recycle above `wait_timeout` | Stale pooled connections | Recycle below timeout and pre-ping | | Replica reads after writes | Stale user-facing state | Pin read-after-write flows to primary |
## Output Expectations
When this skill is used for review, return:
1. Engine/version assumptions. 2. Highest-risk correctness, lock, security, and migration issues. 3. Exact SQL or code changes for the safe path. 4. Validation plan: `EXPLAIN`, migration dry run, lock/deadlock check, and rollback criteria. 5. Any MySQL/MariaDB syntax differences that affect the recommendation.
## Related
- Skill: `postgres-patterns` - PostgreSQL-specific schema and query patterns - Skill: `database-migrations` - migration planning and rollout safety - Skill: `backend-patterns` - API and service-layer patterns - Skill: `security-review` - secret handling, auth, and least privilege - Agent: `database-reviewer` - broader database review workflow
Intended uses
- Use Mysql Patterns when this documented workflow matches the task.
Implementation notes
- Review the source instructions and adapt inputs before running the workflow.
Try it, then tell us how it went
A copied prompt does not tell us whether the task worked. Report your result after checking the output. We collect your tool and outcome, never your files or prompt.