OverviewHistoryStatsSecurity
npx skills add ...
Documentation
SKILL.md
npx skills add affaan-m/ecc --skill 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.
npx skills add affaan-m/ecc --skill 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.
Start by identifying the engine and version:
Keep MySQL and MariaDB guidance separate when syntax differs:
VALUES(col) in
ON DUPLICATE KEY UPDATE; VALUES(col) is deprecated there.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.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 |
Composite index order usually follows equality predicates first, then range or sort columns:
Use EXPLAIN before adding or changing an index:
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.
Cross-engine-compatible form:
MySQL row-alias form:
Use the row-alias form only after confirming the target is MySQL. Use
VALUES(col) for MariaDB or mixed MySQL/MariaDB fleets.
Back it with an index that matches the cursor:
Do not use deep OFFSET pagination on large tables; it makes the server scan
and discard rows before returning the page.
Use JSON columns for extension data, not for fields that need heavy relational filtering or constraints.
For frequently queried JSON paths, expose a generated column and index that column. Keep foreign keys, ownership, tenancy, and lifecycle fields relational.
Use external search when you need typo tolerance, complex ranking, cross-table facets, or language-specific analysis beyond built-in full-text behavior.
Keep transactions short and lock rows in a consistent order:
Deadlock and lock-wait checklist:
UPDATE, DELETE, and locking reads.SHOW ENGINE INNODB STATUS\G soon after a deadlock; it is overwritten
by later events.Queue-style worker claim:
Use SKIP LOCKED only for queue-like workloads where skipping a locked row is
acceptable. It is not a replacement for normal transactional consistency.
SQLAlchemy example:
Node.js mysql2 example:
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.
Useful first-pass commands:
Enable the slow log in a controlled environment:
Use EXPLAIN ANALYZE only when it is safe to execute the query. It runs the
statement and can be expensive on production-sized data.
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.
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 review points:
ALL PRIVILEGES or *.* to application users.Example starting point for a dedicated database host:
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-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 |
When this skill is used for review, return:
EXPLAIN, migration dry run, lock/deadlock check, and
rollback criteria.postgres-patterns - PostgreSQL-specific schema and query patternsdatabase-migrations - migration planning and rollout safetybackend-patterns - API and service-layer patternssecurity-review - secret handling, auth, and least privilegedatabase-reviewer - broader database review workflowCREATE 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;EXPLAIN
SELECT id, total
FROM orders
WHERE account_id = 123 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
setting_value = VALUES(setting_value),
updated_at = CURRENT_TIMESTAMP;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;SELECT id, name, created_at
FROM products
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;CREATE INDEX idx_products_created_id ON products (created_at, id);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;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;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;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;from sqlalchemy import create_engine
engine = create_engine(
"mysql+mysqlconnector://app:secret@db.internal/app",
pool_size=10,
max_overflow=5,
pool_timeout=30,
pool_recycle=240,
pool_pre_ping=True,
connect_args={"connect_timeout": 5},
)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],
);SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G;
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';-- MySQL legacy terminology, still common in existing fleets
SHOW SLAVE STATUS\G;
-- Newer terminology where supported
SHOW REPLICA STATUS\G;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 ''@'%';[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