Backend5 min read

MySQL & MariaDB

Design and optimize MySQL databases - schema design, indexing, query optimization, replication, and connection pooling for production workloads.

MySQLMariaDBSQLRelational DatabaseDatabase

skill: MySQL & MariaDB category: Backend tags: [MySQL, MariaDB, SQL, Relational Database, Database] readingTime: 5

MySQL is the world's most popular open-source relational database. InnoDB (the default engine) provides ACID transactions, row-level locking, and foreign keys. Know the difference between what you put in the schema (enforced constraints) versus application logic, and always test query performance with EXPLAIN before going to production.

Workflow

Design schemas with proper normalizationNormalized schemas reduce redundancy and update anomalies. Third Normal Form (3NF) is the practical target for transactional data.
Index for your query patternsMySQL B-tree indexes turn O(n) full table scans into O(log n) lookups. Wrong or missing indexes are the #1 cause of MySQL performance problems.
Use transactions correctlyInnoDB transactions ensure atomicity - either all changes commit or all roll back. Critical for financial operations and multi-table updates.
Configure connection poolingCreating a MySQL connection takes ~5-20ms. Without pooling, each request creates and tears down a connection - unsustainable at scale.
Set up read replicasMySQL read replicas handle SELECT load, reducing pressure on the primary (write) server. Route reads to replicas, writes to primary.

Output Format

MySQL: InnoDB schema with proper types and indexes, EXPLAIN for query analysis, SQLAlchemy transactions with FOR UPDATE, connection pooling, and read replica routing.

Schema: BIGINT PK + VARCHAR + DATETIME(6) + utf8mb4
Index: composite on (filter_col, sort_col)
EXPLAIN: type != ALL, key != NULL
Pool: connectionLimit=10, parameterized queries
Replication: binlog_format=ROW + ProxySQL

Examples

Input

Output

Edge Cases & Gotchas

ENUM type migrations - Adding a value to an ENUM column in MySQL < 5.6 rebuilds the entire table. Use VARCHAR with a CHECK constraint or a separate lookup table for values that might grow.
utf8 vs utf8mb4 - MySQL's utf8 only stores 3-byte UTF-8 characters - emojis (4-byte) get silently truncated. Always use utf8mb4.
Deadlocks - InnoDB detects deadlocks and kills one transaction. Application must retry on ER_LOCK_DEADLOCK (1213). Add exponential backoff retry logic for all write transactions.
AUTO_INCREMENT gaps - Failed transactions leave gaps in AUTO_INCREMENT sequences. This is normal and expected - don't use IDs for ordering without also storing a created_at timestamp.

When to Break These Rules

Use PostgreSQL instead of MySQL for complex queries needing CTEs, window functions, JSONB, or full-text search - Postgres's SQL capabilities are significantly more powerful.
Use SQLite for single-server applications with low write concurrency - no server to manage, embedded in the application process.

Reference Files

https://dev.mysql.com/doc/
https://use-the-index-luke.com/