How Indexes Supercharge MySQL Queries: A Deep Dive into EXPLAIN

Why Indexes Matter for Large Data

Indexes act like a library catalog for a database, allowing MySQL to locate rows quickly and reduce I/O. When a table reaches a few million rows, performance degrades sharply, so building appropriate indexes becomes essential for big‑data workloads.

Common Causes of Slow SQL

Hardware bottlenecks such as slow network, insufficient memory, low I/O throughput, or full disks.

Missing or ineffective indexes – often caused by deleted rows leaving fragmented index trees; many internet companies use soft deletes for analysis, which can invalidate indexes.

Excessive data volume, which may require sharding (分库分表).

Improper server configuration (e.g., sub‑optimal my.cnf settings).

Analyzing Slow Queries

Enable the slow‑query log and set a threshold (e.g., queries taking longer than 3 seconds).

Use EXPLAIN to inspect the execution plan; identify missing indexes, excessive joins, or design flaws.

Run SHOW PROFILE for a more detailed step‑by‑step breakdown of each operation.

Consult a DBA or operations engineer to tune MySQL server parameters.

What Is an Index?

MySQL defines an index as a data structure that helps retrieve rows efficiently. In practice, most indexes are B+Tree structures; Hash indexes exist but are rarely used for general queries.

The outer blue disk block contains data entries (e.g., 17, 35) and pointers (P1, P2, P3). Leaf nodes store the actual rows, while internal nodes only store keys to guide the search. A three‑level B+Tree can locate a row with just three I/O operations, even for millions of records.

Using EXPLAIN to Analyze Queries

Key columns in the EXPLAIN output:

id : Execution order of statements; lower id runs first.

select_type : Type of SELECT (SIMPLE, PRIMARY, SUBQUERY, UNION, etc.).

table : Table or derived table involved.

type : Access method – the most important indicator of efficiency (system, const, eq_ref, ref, range, index, ALL).

possible_keys : Indexes that could be used.

key : Index actually used.

key_len : Number of bytes of the index used.

ref : Columns or constants compared to the index.

rows : Estimated number of rows examined; lower is better.

extra : Additional information (e.g., using filesort, using temporary, using index, using where).

The type hierarchy from best to worst is:

ALL < index < range ~ index_merge < ref < eq_ref < const < system

ALL indicates a full table scan and is the slowest; index scans are better, and the other types progressively use more of the index, yielding higher performance.

Optimization Case Study

First, create sample tables and insert data:

CREATE TABLE `user_info` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `age` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO user_info (name, age) VALUES ('xys',20),('a',21),('b',23),('c',50),('d',15),('e',20),('f',21),('g',23),('h',50);

CREATE TABLE `order_info` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT(20) DEFAULT NULL,
  `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  `productor` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`user_id`,`product_name`,`productor`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO order_info (user_id, product_name, productor) VALUES
  (1,'p1','WHH'),(1,'p2','WL'),(1,'p1','DX'),(2,'p1','WHH'),(2,'p5','WL'),(3,'p3','MA'),(4,'p1','WHH'),(6,'p1','WHH'),(9,'p8','TE');

Running

EXPLAIN SELECT u.*, o.* FROM user_info u, order_info o WHERE u.id=o.user_id;

shows type = ALL and no index usage. After creating an index on the join column:

CREATE INDEX idx_user_id ON order_info(user_id);

The EXPLAIN output changes: type becomes ref, and rows drops from 9 to 1, demonstrating the performance gain.

Generally, for a left join the index should be on the right table, and for a right join the index should be on the left table.

Should You Create an Index?

Indexes dramatically speed up read queries but add overhead to write operations because each insert, update, or delete must also modify the index structure. An index occupies additional storage space, essentially a separate table that maps indexed columns to primary keys.

Key Takeaways

Use indexes to avoid full table scans; aim for type values like ref, eq_ref, or const.

Enable slow‑query logging and analyze problematic statements with EXPLAIN and SHOW PROFILE.

When adding indexes, consider the impact on write performance and storage.

Regularly review possible_keys vs. key to ensure MySQL is using the intended indexes.