Jan 28, 2026

MySQL Performance Tuning & Troubleshooting Guide: Indexing, Deadlocks, and Bulk Writes

Approximately 80% of backend application performance bottlenecks originate from the database connection layer and query execution inefficiencies. A database schema that works flawlessly with a few thousand records can grind to a halt under production workloads due to missing indexes, transaction locks, or poorly designed primary keys.

This guide consolidates essential performance tuning patterns and troubleshooting workflows for MySQL and relational databases.

1. Database Index Design & Execution Plan Analysis

While indexes dramatically accelerate query speeds when configured correctly, redundant or unoptimized indexes degrade write throughput.

Composite Index Ordering Rules

Equality Condition (=) First: Place columns queried with equality operators (=) at the absolute front of the composite index, followed by range conditions (<, >, between, like) to leverage the full index structure.
High Cardinality First: Prioritize fields with low duplication rates (e.g., email, unique identifiers) over low-cardinality flags (e.g., status, gender).

Covering Indexes

A covering index occurs when all columns requested in a query (including those in the SELECT clause) are completely contained within the index keys. This allows the database engine to skip scanning physical table pages entirely, eliminating expensive random disk I/O (Key Lookups).

Example Query:

-- If there is a composite index on (name, age), no table scan is required
SELECT name, age FROM users WHERE name = 'Alice';

Analyzing `EXPLAIN` Execution Plans

Prepend EXPLAIN or EXPLAIN ANALYZE to your query to review how the database engine executes it.

Understanding type: Look for const, eq_ref, or ref. The type range is acceptable for small spreads. A value of ALL (Full Table Scan) or index (Full Index Scan) indicates optimization is needed.
Understanding Extra: Avoid Using filesort (sorting occurs on disk rather than memory) or Using temporary. These flags suggest you need to adjust your composite index columns to match your query sorting.

2. Resolving Common Query Bottlenecks

The N+1 Query Problem

The N+1 problem occurs when an application fetches a parent record and then executes N subsequent queries to load related child records in a loop.

Root Cause: Often caused by lazy loading configurations in ORMs (like Hibernate/JPA) where associated collections are fetched on-demand rather than eagerly joined.
Resolution: Utilize Fetch Join or Entity Graphs in your ORM to retrieve parent and child records in a single query. In SQL, replace iterative queries with IN clause joins.

Optimizing Pagination

The Pitfall of OFFSET-Based Pagination: Executing queries like LIMIT 1000000, 10 forces MySQL to read one million records into memory, discard them, and return only the last ten. This slows down database response times as pagination deepens.
Cursor-Based (No-OFFSET) Pagination: Track the last seen primary key or sorting value and query using a comparison filter, completely skipping offset scans.
```
-- Optimized pagination query
SELECT * FROM posts WHERE id < 18402 ORDER BY id DESC LIMIT 20;
```

3. High-Throughput Write & Insert Optimizations

When bulk inserting data, individual roundtrip connections slow down operations.

Batch Inserts

Instead of executing singular INSERT statements, send multiple records in a single database payload.

JDBC/Spring Configuration:

# Enable insert batching in Spring Data JPA
spring.jpa.properties.hibernate.jdbc.batch_size=500
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true

Important Warning: When using JPA, the IDENTITY primary key generation strategy (auto-increment) disables Hibernate batch inserts silently. To leverage batch inserts, use a sequence generator or execute bulk SQL queries directly using JdbcTemplate.

The Pitfall of Random UUIDs

Random UUID v4 values lack chronological order. Inserting random strings causes frequent page splits in clustered index leaf pages, destroying disk caching efficiency.

The Solution (UUID v7 / ULID): Transition to UUID v7 or ULID. These formats embed a millisecond timestamp at the beginning of the identifier. They behave sequentially, appending new entries at the end of the index tree and preserving database performance.

4. Isolation Levels, Deadlocks & Replication Lag

Maintaining data integrity while keeping database locking contention low.

Transaction Isolation Levels

MySQL InnoDB defaults to the REPEATABLE READ isolation level.

Preventing Phantom Reads: InnoDB uses Next-Key Locks (a combination of record locks and gap locks) to lock index ranges. This prevents other sessions from inserting new rows within the locked range, blocking phantom reads even under REPEATABLE READ.

Troubleshooting Deadlocks

Deadlocks occur when two concurrent transactions block each other indefinitely, each waiting to acquire a lock held by the other.

Prevention Best Practices:
- Keep transactions as brief as possible.
- Modify tables in the same order across different transactions.
- Diagnose active deadlocks by executing SHOW ENGINE INNODB STATUS; in the terminal to inspect lock wait details.

Replication Lag in Master-Slave Architectures

In read-heavy applications, read replicas (Slaves) can drift behind the primary database (Master) due to single-threaded replication limits under heavy write loads.

Mitigation Strategies:
- Force Master Routing: Route latency-sensitive queries (e.g., loading a detail page immediately after a user creates a post) directly to the Master DB rather than a Slave replica.
- Split large update transactions into smaller batches to prevent replication threads from being hogged by a single update block.

Start Here

Continue with the core guides that pull steady search traffic.