MySQL Index Optimization Strategies — Query Analysis with EXPLAIN

## Structure at a Glance

[WHERE column = value]
        |
        +--> [Index lookup]
        |        |
        |        v
        |    small search range
        |
        +--> [Full table scan]
                 |
                 v
            read every row

The heart of index tuning is not simply “create an index and queries get faster.” It is whether the optimizer can narrow the search down to a small range. In practice, it is much more useful to think of indexes not just as data structures, but as a way for queries to reduce the candidate set quickly.

Query Analysis with EXPLAIN

EXPLAIN SELECT * FROM users WHERE email = 'hoon@test.com';

Column	Meaning
type	Access method (ALL > index > range > ref > const)
key	Index used
rows	Estimated number of rows scanned
Extra	Using index (covering), Using filesort (sort required)

Creating Indexes

-- Single-column index
CREATE INDEX idx_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_status_created ON orders(status, created_at);

-- Unique index
CREATE UNIQUE INDEX idx_unique_email ON users(email);

Composite Index Design Principles

-- Good order: higher-selectivity column first
CREATE INDEX idx_status_date ON orders(status, order_date);
SELECT * FROM orders WHERE status = 'PAID' AND order_date >= '2026-01-01';

-- Index not used: searching without the leftmost column (status)
SELECT * FROM orders WHERE order_date >= '2026-01-01';

Covering Index

-- Query fully served by the index: no table lookup
CREATE INDEX idx_covering ON posts(user_id, title, created_at);
SELECT user_id, title, created_at FROM posts WHERE user_id = 1;
-- Extra: Using index ✅

Cases Where the Index Is Not Used

-- Function on the column
WHERE YEAR(created_at) = 2026
-- Convert to a range query
WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31'

-- Wildcard at the beginning of LIKE
WHERE name LIKE '%hoon%'
-- Wildcard only at the end
WHERE name LIKE 'hoon%'

-- Implicit type conversion
WHERE user_id = '123'   -- when user_id is INT
-- Better
WHERE user_id = 123

Slow Query Log

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 1 second or more
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

What Gets Hard in Production

• MySQL index optimization depends on understanding workload selectivity and access path, not on adding indexes mechanically.
• A slow query can remain slow even with indexes if the predicate order and covering behavior are wrong.
• Write-heavy systems can suffer badly from index sprawl.

Architecture Decisions That Matter

• Design indexes from WHERE, ORDER BY, and join shape together.
• Prefer fewer well-targeted composite indexes over many overlapping single-purpose indexes.
• Use EXPLAIN and production-like cardinality to validate assumptions.

Practical Example

An index is useful only if it matches the query path the optimizer can exploit:

WHERE tenant_id = ? AND deleted = 0 ORDER BY created_at DESC
-> INDEX (tenant_id, deleted, created_at)

Anti-Patterns to Avoid

• Ignoring left-prefix behavior in composite indexes.
• Adding indexes that duplicate each other with minor variations.
• Assuming local test data cardinality predicts production plans.

Operational Checklist

• Review slow query log regularly.
• Check actual cardinality and filtered rows.
• Prune redundant indexes after feature changes.
• Measure write latency after index additions.

Final Judgment

MySQL index tuning is a workload-matching exercise. The winning move is usually fewer, better indexes and better query shape, not more index objects.

Continue Reading

Related posts

🗄️ Database

A Practical Guide to SQL Performance Optimization

A production-focused guide to SQL tuning that treats performance as a workload problem. Learn how to read plans, reduce data access, tune indexes honestly, and avoid premature query heroics.

🗄️ Database

A Practical Database Index Design Playbook

A production-oriented playbook for index design that covers read/write tradeoffs, composite indexes, covering indexes, cardinality, execution plans, anti-patterns, and an operational review workflow.

🖥️ Frontend

Optimizing Core Web Vitals: A Practical Guide to LCP, CLS, and INP

This guide explains Core Web Vitals not as a checklist, but from the perspective of perceived performance and rendering structure. It shows why LCP, CLS, and INP degrade, what to measure first, and how to optimize them in the right order.

📈 Trends

PostgreSQL 18 Trends: What Actually Matters in Practice

PostgreSQL 18 is more than an upgrade headline. AIO, skip scan, better post-upgrade recovery, OAuth, and generated columns all point to a release focused on operational cost reduction.