MySQL performance with InnoDB: indexes, transactions and slow queries

When an application slows down, changing MySQL parameters looks like the fastest intervention. Yet a query that examines unnecessary rows, or a transaction that holds locks while waiting on another system, will keep causing the same problem after the tuning. Diagnosis starts by measuring the work the database is doing.

This article proposes a repeatable review method for MySQL 8.4 with InnoDB tables, in five steps: indexes, execution plans, transactions, locks and configuration. Each recommendation should be checked against the exact installed version and, where applicable, the constraints of the managed service.

Indexes designed from real queries

The starting point is the frequent queries and the operations users notice. An index speeds up locating rows, but it takes space and must be maintained on every write. The index optimization guide describes that trade-off between reads and writes.

The primary key deserves an explicit decision. In InnoDB, its values are included in every secondary index, so an unnecessarily wide key increases the size of each one. The section on optimizing InnoDB queries develops this relationship.

For combined filters, a composite index should order its columns to match the query. MySQL can use the index's leftmost prefixes, according to the multiple-column indexes documentation. An index on (customer_id, created_at) is not equivalent to two independent indexes on each column.

The schema below is illustrative: it queries a customer's orders within a date range. Types and sizes should match the real model, and the proposed index is a hypothesis to be validated against the execution plan and the cost it adds to writes.

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    total DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (id),
    INDEX ix_orders_customer_date (customer_id, created_at)
) ENGINE=InnoDB;

EXPLAIN
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
  AND created_at >= '2026-01-01 00:00:00'
  AND created_at < '2026-02-01 00:00:00'
ORDER BY created_at;

The filter expresses the range with explicit bounds and leaves the date column without functions applied, which lets the optimizer use the index. Two checks remain: the time zone the stored values represent and, if a stable order is needed among orders with the same timestamp, the tie-breaker, which may change the plan.

Reading the execution plan

EXPLAIN shows how MySQL plans to execute a statement: the access to each table, the chosen index and the estimated rows. A full scan is not necessarily a defect; it may be the right strategy on a small table or for a query that needs most of the data.

In MySQL 8.4, EXPLAIN ANALYZE executes the query and reports what was observed, so it should only be used where that execution is acceptable. The EXPLAIN reference details its behavior and formats. It is advisable to start with a controlled SELECT and data representative of the problem.

The analysis compares estimated and observed rows and identifies steps that repeat work. The query, its parameters and the plan are recorded before the change, and the measurement is repeated afterwards. The appearance of an index name in the plan does not, on its own, prove the problem is solved.

The transaction as a unit of work

A transaction should correspond to a business operation that must be committed as a whole. With autocommit enabled, each statement commits individually; START TRANSACTION groups work until COMMIT or ROLLBACK, according to the InnoDB transactions documentation.

The transaction should cover only the database work that shares that boundary. As a design criterion, it should not contain human waits or remote calls, because they prolong lock retention. If the process combines a local update with an external service, it must be documented how it recovers when either step fails.

Driver error handling is also part of the design. After a failed statement, the application decides whether to continue or roll back, and returns the connection to the pool in a known state. An exception caught without that decision can leave pending work for a later request.

Schema migrations should not be mixed with the business transaction. MySQL documents statements that cause an implicit commit, including several DDL operations, so they cannot be rolled back with the rest. The official list should be consulted before designing a rollback procedure.

Locks and deadlocks

A deadlock occurs when concurrent transactions request resources in incompatible orders. InnoDB detects it, rolls back one of them, and the application must be ready to retry it. The guide to minimizing and handling deadlocks recommends short transactions and a consistent access order.

Two reviews reduce their frequency: operations on multiple rows should follow the same order, and update statements should have suitable indexes, because locating rows is part of the work that holds locks. Changing the isolation level, by contrast, requires concurrency and business-rule testing in addition to timing measurements.

Retries should be bounded and repeat the whole transaction. When the operation also produces external effects, the design must prevent those effects from repeating along with the local work. That is a business-process decision and should be visible in error handling.

Slow queries as evidence

The slow query log identifies statements that are candidates for review, according to its configuration. Its main controls are long_query_time and min_examined_row_limit, described in the log documentation. The observation window should represent the load under investigation.

Capturing queries without a reading procedure only accumulates files. A review owner, the log location and the management of its growth must be defined. Prioritization combines each pattern's cumulative cost, its frequency and its impact on the application.

For each selected query, its conditions are reproduced and a concrete change is proposed: fewer requested rows, a modified filter or a new index, preserving the functional result. Plan and execution are then compared, and any increase in write cost is recorded.

Memory and durability

The buffer pool keeps InnoDB data and index pages in memory. Its size must account for the available memory and the server's other consumers, according to the configuration guide. Before increasing it, it is worth confirming that the observed problem is caused by physical reads and not something else.

Durability depends on innodb_flush_log_at_trx_commit and, with binary logging enabled, on sync_binlog. The manual recommends a value of 1 for both for the greatest durability and consistency in a replication setup with InnoDB transactions. The InnoDB variables document this; actual behavior also depends on the storage.

In MySQL 8.4, redo log capacity is controlled with innodb_redo_log_capacity, according to the redo log reference. Recipes written for earlier versions should therefore be reviewed before being applied. Every parameter change is recorded with its previous value and a rollback criterion.

Closing record for an intervention

An intervention ends with a record that explains why the change was accepted and what behavior should be monitored afterwards:

• Exact MySQL version and relevant configuration.
• Query, parameters and representative data volume.
• Execution plan and measurement, before and after the change.
• Functional result and behavior of concurrent writes.
• Rollback procedure and follow-up owner.

Xterra Engine performs MySQL performance diagnostics with this method, and delivers each change with its measurement.