Advanced SQL optimization: from slow query to instant query

Composite indexes, execution plans, join anti-patterns and transactions: the method to diagnose and fix the queries that slow an application down.

Measure before optimizing: execution plan first

A slow query without an execution plan is a guess. `EXPLAIN` gives the estimated strategy, `EXPLAIN ANALYZE` the real costs. Check the access `type` (ALL vs ref vs range), the estimated row count, temporary files and sorts. A gap between estimate and reality often means stale statistics: `ANALYZE TABLE` refreshes them.

EXPLAIN ANALYZE
SELECT qs.id, qs.score
FROM quiz_session qs
WHERE qs.tenant_id = 42
  AND qs.taken_at >= '2026-01-01'
ORDER BY qs.score DESC
LIMIT 20;

Designing effective composite indexes

The column order of a composite index follows the predicates: equality first, range next, sort last. An index covering all columns of the query avoids table lookups. Every index costs writes: drop those serving a single rare query, and check actual usage with engine statistics.

-- Good order: equality (tenant_id), range (taken_at), sort (score)
CREATE INDEX idx_qs_tenant_taken_score
    ON quiz_session (tenant_id, taken_at, score);

-- Covered query: no table lookups
SELECT score FROM quiz_session
WHERE tenant_id = 42 AND taken_at >= '2026-01-01'
ORDER BY score DESC;

Join and subquery anti-patterns

A join that multiplies rows (fan-out) skews aggregates: `COUNT(*)` counts joined rows, not entities. Correlated subqueries executed per row are often replaceable by window functions or derived joins. Always check that join columns are indexed on both sides and that the join type matches the business intent.

-- Fan-out: one session has several answers
SELECT s.id, COUNT(*) AS answer_count
FROM quiz_session s
JOIN quiz_answer a ON a.session_id = s.id
GROUP BY s.id;

-- Alternative without duplicates: derived subquery
SELECT s.id,
       (SELECT COUNT(*) FROM quiz_answer a WHERE a.session_id = s.id) AS answer_count
FROM quiz_session s;

Functions on columns: the silent index killer

Applying a function to a column in the WHERE clause (`LOWER(email) = ?`, `DATE(created_at) = ?`) prevents index usage: the engine must evaluate the function on every row. Work around it with a generated and indexed column, a functional index (MySQL 8.0.13+), or by rewriting the predicate as a range. The rule: keep the column bare in predicates.

-- Avoid: function on the column
SELECT * FROM users WHERE LOWER(email) = 'x@example.com';

-- MySQL 8: functional index
CREATE INDEX idx_users_lower_email ON users ((LOWER(email)));

-- or explicit range on a timestamp
SELECT * FROM quiz_session
WHERE taken_at >= '2026-08-01' AND taken_at < '2026-08-02';

SARGable predicates and cardinality

A predicate is SARGable (Search ARGument Able) when the engine can exploit it through an index: direct comparisons, `IN`, `BETWEEN`, prefix `LIKE 'abc%'`. `LIKE '%abc'`, `!=` and unindexed `OR` often break this property. Cardinality also guides the choice: an index on a nearly constant column (99% “active” status) will not be used, and that is normal.

-- SARGable: usable prefix
WHERE slug LIKE 'symfony-%';

-- Not SARGable: suffix → full scan
WHERE slug LIKE '%-optimization';

Transactions, locks and deadlocks

Deadlocks are not random bugs: they are lock interleavings. Reduce them by locking resources in a constant global order, keeping transactions short and avoiding read-then-write on the same row without need. Isolation levels have a cost: SERIALIZABLE locks ranges, READ COMMITTED is often enough. A deadlock should be retried, not ignored.

// Constant order: always lock sessions BEFORE results
$conn->executeQuery('SELECT id FROM quiz_session WHERE id = ? FOR UPDATE', [$sessionId]);
$conn->executeQuery('INSERT INTO quiz_result (session_id, score) VALUES (?, ?)', [$sessionId, $score]);

// On deadlock (SQLSTATE 40001): retry with backoff

Windows and aggregates: replacing self-joins

Window functions compute per-group values without losing detail rows: rankings, deltas between rows, running totals. They replace expensive self-joins and correlated subqueries. The `OVER (PARTITION BY ... ORDER BY ...)` defines the frame; verify that the partition matches the real business unit (user, session, tenant).

SELECT
    user_id,
    taken_at,
    score,
    AVG(score) OVER (PARTITION BY user_id ORDER BY taken_at
                     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg
FROM quiz_result
ORDER BY user_id, taken_at;

Data modeling: normalization, types and constraints

A clean schema avoids painful optimizations: numeric types for numbers (never VARCHAR for scores), integrity constraints (FK, CHECK, NOT NULL) that guarantee exploitable data, and partitioning reserved for very large tables. JSON columns are convenient but query poorly: structure what is actually searched, keep JSON for what is opaque.

CREATE TABLE quiz_session (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    score DECIMAL(5,2) NOT NULL,
    taken_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    CONSTRAINT fk_session_tenant FOREIGN KEY (tenant_id) REFERENCES tenant (id),
    CONSTRAINT fk_session_user FOREIGN KEY (user_id) REFERENCES user (id),
    INDEX idx_session_tenant_taken (tenant_id, taken_at)
) ENGINE=InnoDB;