Why Adding Indexes to Every Column Won’t Speed Up MySQL Queries

A Slow Query Prompted Reflection

MySQL is the default relational database for most internet companies because of its performance, low cost, and rich ecosystem, but mastering its use—especially query optimization—is a must‑have skill for developers. The author, who has been optimizing slow queries at Meituan since July 2013, shares insights drawn from hundreds of real cases.

MySQL Index Principles

Index Purpose

An index works like a dictionary: it lets the engine locate rows quickly instead of scanning the whole table. Without an index, every row must be examined to find matching data.

Index Theory

Indexes reduce the search space by repeatedly narrowing the range of possible rows, turning random I/O into sequential I/O. They must handle equality, range, LIKE, and set operations.

Disk I/O and Prefetch

Disk access consists of seek time, rotational latency, and transfer time—roughly 5 ms + 4 ms ≈ 9 ms per I/O. Since a CPU can execute hundreds of thousands of instructions in that time, each disk I/O is extremely costly. Operating systems therefore read an entire page (typically 4 KB or 8 KB) and prefetch adjacent pages to amortize the cost.

Index Data Structure

Because the goal is to keep the number of disk I/Os as low as possible, a multi‑way search tree with a small height is ideal. The B+‑tree satisfies this requirement and became the standard index structure in MySQL.

Detailed B+‑Tree

Each disk block (a leaf) stores several data items and pointers. Real data lives in leaf nodes; internal nodes only store guide keys.

B+‑Tree Search Process

To find a key, the engine loads the root block, performs a binary search to choose the correct child pointer, loads that child, and repeats until reaching a leaf. A three‑level B+‑tree can locate a row with only three I/Os, compared with a million I/Os for a full table scan.

B+‑Tree Properties

The height h ≈ log m+1 (N), where N is the number of rows and m is the number of items per block. Smaller keys (e.g., INT 4 bytes) reduce m, thus reducing height. Composite indexes follow the left‑most prefix rule: the optimizer can use the index only up to the first range condition.

Slow Query Optimization

Understanding index theory is only half the battle; the other half is applying systematic steps to diagnose and fix slow queries.

Index‑Building Principles

Left‑most prefix rule : MySQL matches columns from left to right until a range condition appears.

Equality and IN can be reordered : MySQL can rearrange = and IN predicates to fit the index.

High‑cardinality columns are preferred; the distinct‑value ratio should be > 0.1 for good selectivity.

Avoid functions on indexed columns ; expressions like FROM_UNIXTIME(col) prevent index usage.

Prefer covering or extended indexes over creating new ones.

Back to the Original Slow Query

The sample query filters on status, operator_id, type, and a time range. According to the left‑most rule, a composite index on (status, operator_id, type, operate_time) (order of the first three can be swapped) is optimal.

Explain Command – The Optimization Toolbox

The EXPLAIN output shows the number of rows examined ( rows) and the access type. Reducing rows is the primary way to speed up a query.

Basic Steps of Slow‑Query Optimization

Confirm the query is truly slow (disable query cache).

Identify the table that returns the fewest rows and start the plan there.

Use EXPLAIN to verify the plan matches expectations.

Understand the business use‑case.

Apply the index‑building principles.

Iterate until the observed performance meets the target.

Several Real‑World Slow‑Query Cases

Complex Statement Writing

SQL that merely “works” can be dramatically slower than an equivalent statement that respects index usage. The article rewrites a multi‑join query with DISTINCT into two UNION sub‑queries that each can use appropriate indexes, reducing execution time from 1.87 s to 10 ms.

SELECT emp.id FROM cm_log cl INNER JOIN employee emp ON cl.ref_oid = emp.id WHERE cl.last_upd_date > '2013‑11‑07 15:03:00' AND cl.last_upd_date < '2013‑11‑08 16:00:00' AND cl.ref_table = 'Employee' AND emp.is_deleted = 0 UNION SELECT emp.id FROM cm_log cl INNER JOIN emp_certificate ec ON cl.ref_oid = ec.id WHERE …

Understanding Application Scenarios

In a table stage_poi, the columns accurate_result and sync_status have very low cardinality, so adding separate indexes would not help. By analyzing the business pattern (periodic scans that only return a few hundred rows), a composite index (accurate_result, sync_status) reduces query time from 6.22 s to 0.20 s.

ALTER TABLE stage_poi ADD INDEX idx_acc_status(accurate_result, sync_status);

Unoptimizable Queries

Some queries involve deep joins and large intermediate result sets that cannot be reduced by indexing alone. For example, a multi‑join query on contact, contact_branch, branch_user, and org_emp_info with a LIMIT clause ends up sorting millions of rows before applying the limit, leading to execution times of over 13 seconds. Re‑ordering the join to filter early helps, but the nested‑loop nature of MySQL can still make the query impractical.

The key takeaway is that not every slow query can be fixed at the database layer; sometimes the application logic must be revisited.

Final Thoughts

Understanding MySQL’s index internals—especially the B+‑tree, disk‑I/O behavior, and left‑most prefix rule—makes diagnosing slow queries much easier. However, database‑level tuning can only go so far; optimizing the surrounding application is often the decisive factor.

Source: Meituan Tech Blog – http://tech.meituan.com/mysql-index.html