How Indexes Supercharge MySQL Queries: A Deep Dive into EXPLAIN

Why Indexes Matter for Large Datasets

Indexes work like a library catalog, allowing MySQL to locate rows quickly and reduce I/O. When a table reaches a few million rows, query performance can degrade sharply, so adding appropriate indexes becomes essential.

Common Causes of Slow SQL

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

Missing indexes or indexes that have become ineffective (e.g., after deletions that leave gaps in the index tree).

Excessive data volume, often addressed with sharding or partitioning.

Sub‑optimal server configuration (my.cnf settings).

Finding the Right Starting Point

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

Run EXPLAIN on slow queries to see whether indexes are used.

Use SHOW PROFILE for a more detailed execution breakdown, showing how many seconds each step consumes.

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

What Is an Index?

MySQL defines an index as a data structure that helps retrieve rows efficiently. The most common type is a B+Tree index; a hash index is used only in specific scenarios. The B+Tree stores keys in sorted order, allowing binary search with only a few I/O operations.

Using EXPLAIN

First create test tables and insert sample 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),('i',15);

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');

Run

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

and examine the output columns:

id

Shows the order in which tables are accessed. When ids are equal, execution follows the order listed; when ids differ, larger ids are processed first.

select_type

Indicates the type of SELECT (e.g., SIMPLE, PRIMARY, SUBQUERY, UNION, DERIVED, etc.).

table

Names the table or derived table involved in the step.

type

Crucial for performance; values range from system (single row) to ALL (full table scan). Common values include const, eq_ref, ref, range, index, and ALL. The hierarchy of efficiency is

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

.

possible_keys

Indexes that MySQL could consider using for the query.

key

The index actually chosen. If key is NULL, no index is used.

key_len

Number of bytes of the index used; helps evaluate whether a composite index is fully utilized.

ref

Shows which column or constant the index column is compared to.

rows

Estimated number of rows MySQL expects to examine; lower values indicate better selectivity.

extra

Additional information such as using filesort, using index, using temporary, or using where that can highlight further optimization opportunities.

Optimization Example

Before adding an index, a join query shows type=ALL and scans nine rows:

After creating an index on the join column, type changes to ref, key shows the new index, and rows drops from 9 to 1:

Typical rule: place the left‑side index on the right table for a LEFT JOIN, and vice‑versa for a RIGHT JOIN.

Should You Create an Index?

Indexes dramatically speed up reads but add overhead to writes because each insert, update, or delete must also modify the index structure. An index occupies additional storage space and can affect write throughput.