Best Practices — MySQL

MySQL Best Practices

These best practices distill years of production MySQL experience into a concise, actionable guide. They cover schema design, query writing, indexing, transactions, connections, security, and operations — the things that separate a fragile hobby project from a reliable production system.

Schema Design Best Practices

Always use InnoDB:

Feature	InnoDB	MyISAM
ACID transactions	Yes	No
Row-level locking	Yes	Table-level only
Foreign keys	Yes	No
Crash recovery	Automatic	Manual repair needed
Concurrent reads + writes	Excellent	Poor (table lock on write)

SQL

-- Verify engine at table level
SHOW TABLE STATUS WHERE Name = 'orders'G

-- Convert a MyISAM table to InnoDB
ALTER TABLE legacy_table ENGINE = InnoDB;

Use utf8mb4 everywhere:

Bash

# my.cnf
[mysqld]
character_set_server = utf8mb4
collation_server     = utf8mb4_unicode_ci

SQL

-- Specify explicitly on every database and table
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Note

MySQL's older utf8 charset silently truncates 4-byte characters (emoji, many Chinese characters). Use utf8mb4 always — it is the correct, full UTF-8 implementation.

Always have a primary key with explicit NOT NULL and DEFAULT values:

SQL

-- Preferred: surrogate BIGINT PK for tables that will grow
CREATE TABLE orders (
  id          BIGINT       NOT NULL AUTO_INCREMENT,
  customer_id INT          NOT NULL,
  status      VARCHAR(20)  NOT NULL DEFAULT 'pending',
  total       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
                                    ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Junction table: composite PK, no surrogate needed
CREATE TABLE user_roles (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  granted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Tip

Use BIGINT for surrogate keys on tables that will grow large. INT maxes out at about 2.1 billion rows — an unexpected limit to hit in production at 2 AM.

Indexing Best Practices

Index every foreign key column — MySQL does NOT automatically index FK columns unlike PostgreSQL.
Prefer a well-designed composite index over multiple single-column indexes for multi-column queries.
Do not over-index write-heavy tables — every extra index slows down INSERT, UPDATE, and DELETE.
Use covering indexes for your most frequent read queries (include SELECT columns in the index).
Drop unused and redundant indexes — use sys.schema_unused_indexes and sys.schema_redundant_indexes.

SQL

-- Always index FK columns
CREATE TABLE orders (
  id          INT NOT NULL AUTO_INCREMENT,
  customer_id INT NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_customer_id (customer_id),   -- required for FK performance!
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Find unused indexes (run after sufficient traffic)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'myapp';

-- Find redundant indexes
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'myapp';

Query Best Practices

*Never SELECT :

SQL

-- Bad: fetches all columns including large TEXT/BLOB fields you don't need
SELECT * FROM users WHERE id = 42;

-- Good: fetches only what the app needs, enables covering indexes
SELECT id, username, email, created_at FROM users WHERE id = 42;

Always parameterise queries:

Bash

// Node.js — always use execute() not query() for user input
const [rows] = await connection.execute(
  'SELECT * FROM users WHERE email = ? AND status = ?',
  [userEmail, 'active']
);

Avoid functions on indexed columns in WHERE:

SQL

-- BAD: function on indexed column prevents index use
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- GOOD: range comparison uses the index
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- BAD: function on indexed column
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- GOOD: use case-insensitive collation on the column instead
-- ALTER TABLE users MODIFY email VARCHAR(255) COLLATE utf8mb4_unicode_ci;
SELECT * FROM users WHERE email = 'alice@example.com';  -- CI comparison via collation

Use EXPLAIN on every non-trivial query:

type: ALL on tables with more than a few thousand rows — add an index.
Extra: Using filesort — add an index matching the ORDER BY columns.
rows estimates that are large — the query is doing unnecessary work.
Extra: Using temporary — GROUP BY or ORDER BY is using a temp table; add or refine indexes.

Transaction Best Practices

Wrap related writes in a transaction to ensure atomicity. If one statement fails, the entire operation rolls back.

SQL

START TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (42, 99.99);
SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, qty)
VALUES (@order_id, 7, 2);

UPDATE inventory SET qty = qty - 2 WHERE product_id = 7;

COMMIT;  -- or ROLLBACK on error

Warning

Keep transactions short. A long open transaction holds row locks, blocking other queries and potentially causing deadlocks. Never leave a transaction open during user input, HTTP calls, or any I/O operation.

Transaction best practices:

Do all computation and data fetching before starting the transaction.
Issue all DML statements (INSERT/UPDATE/DELETE) inside the transaction.
COMMIT immediately when done — do not wait for user confirmation.
Always handle errors with an explicit ROLLBACK in application code.
Never make HTTP calls, sleep, or do user-facing I/O inside an open transaction.

Connection Best Practices

Creating a new MySQL connection is expensive (TCP handshake, authentication, session setup). Always use a connection pool in your application.

Bash

// mysql2 connection pool — Node.js
const pool = mysql.createPool({
  host: 'localhost',
  user: 'app',
  password: process.env.DB_PASSWORD,
  database: 'myapp',
  connectionLimit: 10,    // tune based on server capacity
  waitForConnections: true,
  queueLimit: 0
});

Bash

# my.cnf — connection timeouts
[mysqld]
# Kill idle connections after 10 minutes
wait_timeout        = 600
interactive_timeout = 3600
# Max simultaneous connections
max_connections     = 200

Tip

Set wait_timeout and ensure your connection pool validates connections before use. Stale connections that time out on the server side cause application errors if the pool hands them out without checking.

Security Best Practices

Never connect production applications as root — create dedicated accounts with minimal privileges.
Grant only the specific privileges each account needs (SELECT-only for reports, DML for app).
Use MySQL roles (8.0+) to manage permission sets for groups of users.
Store passwords with bcrypt (cost factor 12+) — never MD5 or SHA1.
Use TLS/SSL for connections, especially between application servers and the database.
Audit superuser accounts quarterly and remove any that are no longer needed.

Avoid SELECT in Loops — N+1 Problem

Bash

// Bad: 1 query per user (N+1) — 1000 users = 1000 queries
for (const user of users) {
  const orders = await db.execute('SELECT * FROM orders WHERE user_id = ?', [user.id]);
}

// Good: one query for all users — always prefer batch over loop
const ids = users.map(u => u.id);
const [orders] = await db.execute(
  'SELECT * FROM orders WHERE user_id IN (?)',
  [ids]
);

Operational Best Practices

Backup and test restore regularly:

Bash

# Daily logical backup with mysqldump
mysqldump --single-transaction --routines --triggers   --all-databases -u root -p | gzip > backup_$(date +%F).sql.gz

# Weekly restore test (on staging)
gunzip < backup_2024-06-15.sql.gz | mysql -u root -p staging_db

# Verify row counts after restore
mysql -u root -p -e "SELECT COUNT(*) FROM staging_db.orders;"

Tip

For large databases (more than a few GB), use Percona XtraBackup for hot physical backups that do not lock the database during the backup.

Monitor slow queries:

Bash

# my.cnf
slow_query_log      = ON
long_query_time     = 1
slow_query_log_file = /var/log/mysql/slow.log

# Analyse with pt-query-digest
pt-query-digest /var/log/mysql/slow.log | head -100

Upgrade MySQL regularly:

Stay on a supported major version — MySQL 5.7 reached EOL in October 2023.
MySQL 8.0 brought window functions, CTEs, roles, atomic DDL, and utf8mb4 as default.
Test upgrades on staging with production data first; check for deprecated syntax in slow logs.

Common Anti-Patterns

Anti-Pattern	Problem	Fix
SELECT *	Fetches unused data, prevents covering indexes	Name only the columns you need
No primary key	InnoDB creates a hidden PK; replication breaks	Add a BIGINT AUTO_INCREMENT PK
Functions on indexed columns in WHERE	Index cannot be used, full scan	Rewrite as range predicate
N+1 queries in loop	Thousands of round-trips for one logical operation	Use IN() or JOIN to batch
Transactions left open	Holds locks, causes deadlocks	COMMIT or ROLLBACK immediately
Storing JSON in TEXT	No validation, no path extraction	Use the JSON column type
FLOAT/DOUBLE for money	Rounding errors in financial calculations	Use DECIMAL(15,2)
No FK indexes	Full scan on every FK lookup	Add INDEX on every FK column
utf8 charset (not utf8mb4)	Emoji and 4-byte chars truncated silently	Use utf8mb4 everywhere
Connecting as root from app	One breach = full server compromise	Create least-privilege app account