Understanding MySQL Indexes and Using EXPLAIN for Query Optimization

Indexes dramatically improve query speed in large datasets, and MySQL's EXPLAIN command helps analyze whether a query uses the appropriate indexes.

1. Reasons for Slow SQL Execution

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

Missing or ineffective indexes (e.g., after deletions the index tree may become fragmented).

Excessive data volume (necessitating sharding or partitioning).

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

2. Finding the Starting Point for Analysis

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

Use EXPLAIN and slow‑query analysis to spot bad SQL, missing indexes, or overly complex joins.

Use SHOW PROFILE for more detailed execution steps.

Consult a DBA or operations team for server‑level tuning.

3. What Is an Index?

MySQL defines an index as a data structure that helps retrieve rows efficiently; essentially it is a sorted structure that allows fast look‑ups. The most common index type is the B+Tree, while hash indexes are used in specific scenarios.

The B+Tree stores only the leaf nodes with actual row data; internal nodes contain only key values and pointers, reducing I/O operations dramatically even for millions of rows.

4. Using EXPLAIN – Practical Examples

First, create sample tables and 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 observe the columns:

id

Shows the order of table processing; lower id values are evaluated first.

select_type

Indicates the query type (SIMPLE, PRIMARY, SUBQUERY, UNION, etc.).

table

Lists the tables or derived tables involved.

type

Crucial for performance: possible values include system, const, eq_ref, ref, range, index, and ALL, ordered from best to worst.

possible_keys

Indexes that MySQL could use for the query.

key

The actual index chosen by the optimizer.

key_len

Number of bytes of the index used.

ref

Shows which column or constant the index is matched against.

rows

Estimated number of rows MySQL must examine; lower is better.

extra

Additional information such as Using filesort, Using index, Using temporary, or Using where.

5. Optimization Case Study

Original query without indexes shows type = ALL and scans many rows. After adding an index on the join column, type changes to ref, rows drops from 9 to 1, and the query becomes much faster.

6. Should You Create an Index?

Indexes speed up reads but add overhead to writes and consume storage because each index is essentially a separate table mapping keys to row locations.

The article concludes with a disclaimer that the author is a regular developer and welcomes feedback.