MENU


Common Anti Patterns

This page collects recurring, easy-to-miss mistakes that quietly degrade MySQL performance – each is cheap to fix once spotted, and each is a common cause of the symptoms covered elsewhere in this chapter.

N+1 Queries

An N+1 pattern runs one query to fetch a list of N rows, then one additional query per row to fetch related data – N+1 round trips where a single join or a single batched query would do. It's rarely written deliberately; it's the default behavior of naive ORM lazy-loading.

-- N+1: one query, then one per customer (application-side loop) SELECT id FROM customers WHERE country = 'DE'; -- ... for each returned id: SELECT * FROM orders WHERE customer_id = ?;

Batched fix: fetch all related rows in one query, then group them in the application – or join directly if the shapes allow it.


SELECT o.* FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.country = 'DE'; -- or, if the ORM needs separate result shapes: SELECT * FROM orders WHERE customer_id IN (/* all ids from the first query */);

Either replaces N round trips with one. Each extra round trip costs network latency on top of the query's own execution time, so N+1 hurts most when N is large or latency to the database is non-trivial (e.g. cross-AZ).


Unindexed Foreign Keys

InnoDB requires an index on the referencing (child) columns of a foreign key only if one doesn't already exist as a side effect of another index – and MySQL creates one automatically when a FOREIGN KEY is declared without a matching index already present. The real danger is a foreign-key-shaped column added later by hand, or a relationship enforced only in application code, with no supporting index at all: every join on that column becomes a full scan of the child table, and every delete/update cascade check on the parent scans the child table to look for references.

-- orders.customer_id relates to customers.id but has no index EXPLAIN SELECT * FROM orders WHERE customer_id = 42; -- type: ALL -- full table scan

Fix: ALTER TABLE orders ADD INDEX idx_customer_id (customer_id); – cheap to add, and it's what a declared FOREIGN KEY would have created automatically.


Implicit Type Conversion

Comparing a column to a value of a different type forces MySQL to convert one side before comparing – and when the conversion falls on the indexed column itself rather than the constant, the index can no longer be used for a range or equality lookup, same as wrapping the column in a function (see Query Rewriting).

-- orders.customer_ref is VARCHAR, compared to an integer literal EXPLAIN SELECT * FROM orders WHERE customer_ref = 42; -- MySQL converts customer_ref to a number for every row -- index unusable

Fix: match types explicitly – WHERE customer_ref = '42' – or, better, don't store semantically numeric keys as strings (or vice versa) in the first place.


The same issue appears with character sets and collations: joining or comparing columns with different collations can silently disable an index even when both sides are the correct data type.

Over-Indexing (Write Amplification)

Every index is a separate on-disk structure that must be updated on every INSERT, UPDATE of an indexed column, and DELETE – so each additional index adds write cost and additional redo/undo log volume, even though it never appears in a read-side EXPLAIN. A table with a dozen speculative indexes "just in case" can turn a cheap single-row insert into a dozen B-tree updates.

SELECT * FROM sys.schema_unused_indexes; SELECT * FROM sys.schema_redundant_indexes;

See Performance Schema and Sys Schema – periodically check both, and drop indexes that are neither used nor covering a real query pattern. Favor a smaller number of well-chosen composite/covering indexes (see Index Strategy) over many narrow single-column ones.


Huge Unbounded IN() Lists

WHERE col IN (...) with a handful of values is fine and often optimized like a series of index lookups, but a list built dynamically from application data (e.g. "all ids currently in the user's cart") can silently grow to thousands or tens of thousands of values. Consequences: query parsing/optimization overhead grows with list size, the statement can exceed max_allowed_packet, and the optimizer may abandon an efficient per-value index lookup in favor of a full scan once the list is long enough that scanning looks cheaper.

-- built from thousands of application ids SELECT * FROM orders WHERE id IN (1, 2, 3, /* ...thousands more... */);

Fix: load the candidate ids into a temporary table (or a derived table via a single batched insert) and join against it instead, or cap/paginate the list at the application layer, or use JOIN against a values-producing subquery.


CREATE TEMPORARY TABLE tmp_ids (id BIGINT UNSIGNED PRIMARY KEY); INSERT INTO tmp_ids VALUES (1), (2), (3) /* ... batched ... */; SELECT o.* FROM orders o JOIN tmp_ids t ON t.id = o.id;

A join against an indexed temporary table scales far better than a multi-thousand-value IN() list, and keeps the statement text itself small.


CREATE TABLE customers (
  id       INT UNSIGNED PRIMARY KEY,
  country  CHAR(2) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  id             BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id    INT UNSIGNED NOT NULL,   -- unindexed FK-shaped column
  customer_ref   VARCHAR(20) NOT NULL,    -- stores numeric-looking ids as text
  status         VARCHAR(20) NOT NULL
) ENGINE=InnoDB;

-- N+1 fix: batch instead of looping per-row in the application
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'DE';

-- Unindexed foreign key: full scan
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

-- Implicit type conversion defeats the index
EXPLAIN SELECT * FROM orders WHERE customer_ref = 42;   -- bad: numeric literal
EXPLAIN SELECT * FROM orders WHERE customer_ref = '42'; -- good: matches column type

-- Over-indexing: find unused/redundant indexes before adding more
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

-- Huge IN() list -> batch through a temporary table instead
CREATE TEMPORARY TABLE tmp_ids (id BIGINT UNSIGNED PRIMARY KEY);
INSERT INTO tmp_ids VALUES (1), (2), (3);
SELECT o.* FROM orders o JOIN tmp_ids t ON t.id = o.id;