Master MySQL Performance: Deep Dive into Indexes and Explain Plans

Introduction

Many data analysts spend minutes analyzing data but hours waiting for query results; therefore, SQL execution efficiency is as important as correct results.

MySQL Basic Architecture

1) MySQL architecture diagram

The left side represents various clients (command‑line, WorkBench, Navicat, etc.). The right side is the MySQL server, divided into the SQL layer and the storage‑engine layer. After a query is executed, results are stored in the query cache for future reuse or sent directly to the client.

2) Viewing storage engines

Run show engines; and show variables like "%storage_engine%"; to list available engines.

3) Creating a table with a specific engine

create table tb(
    id int(4) auto_increment,
    name varchar(5),
    dept varchar(5),
    primary key(id)
) engine=myISAM auto_increment=1 default charset=utf8;

Why Optimize SQL?

Complex joins, sub‑queries, or poorly written statements can cause long server execution times, so learning optimization techniques is essential.

SQL Optimization Process

1) Writing process

select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

2) Parsing process

from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

Detailed parsing steps are described in this article .

Index Fundamentals

Indexes act like a dictionary’s table of contents, allowing fast location of rows. MySQL primarily uses B‑tree structures, specifically B+ trees.

1) What is an index?

An index is a data structure that helps MySQL retrieve rows efficiently; it is usually a B+ tree.

2) Visualizing an index with a binary tree

When an index on column age exists, the engine can quickly locate the row by traversing the tree instead of scanning the whole table.

Index Drawbacks

Large data sets produce large indexes that consume memory or disk space.

Indexes are unsuitable for small tables, frequently updated columns, or rarely queried columns.

Indexes speed up reads but slow down inserts, updates, and deletes because both the table and the index must be modified.

Index Benefits

Reduced I/O and faster query execution.

Lower CPU usage; ordered indexes can avoid costly sorting operations.

Index Types and Creation

1) Single‑column index

Created on a single column; a table can have many such indexes.

2) Unique index

Ensures column values are unique (e.g., primary key, student ID).

3) Composite index

Combines multiple columns (e.g., (name, age)) to improve multi‑column queries.

Creating indexes – syntax

create index index_name on tb(dept);

create unique index name_index on tb(name);

create index dept_name_index on tb(dept, name);

Alternative method using ALTER TABLE:

alter table tb add index dept_index(dept);

alter table tb add unique index name_index(name);

alter table tb add index dept_name_index(dept, name);

If a column is a primary key, it automatically has a primary‑key index, which behaves like a unique index but disallows NULL values.

Dropping and Viewing Indexes

drop index name_index on tb;

show index from tb;

Exploring SQL Performance with EXPLAIN

1) Manual optimization – use EXPLAIN to view the execution plan and identify costly operations.

2) Automatic optimizer adjustments – MySQL may rewrite a query to a more efficient form, which can sometimes interfere with manual tuning.

Key EXPLAIN columns:

id : execution step identifier.

select_type : query type (simple, primary, subquery, derived, union, etc.).

table : table name.

type : join type (system, const, eq_ref, ref, range, index, ALL).

possible_keys : indexes that could be used.

key : index actually used.

key_len : length of the used index portion.

rows : estimated rows examined.

Extra : additional info (using filesort, using temporary, using index, etc.).

Examples of common EXPLAIN outputs and their interpretations are provided throughout the article, covering simple selects, sub‑queries, derived tables, UNIONs, and various join types.

Common Optimization Tips

Prefer the leftmost prefix of a composite index (best‑left‑prefix).

Avoid functions, calculations, or type conversions on indexed columns.

Do not use !=, <>, or IS NULL on indexed columns; they can invalidate the index.

Range conditions ( >, <, IN) may cause subsequent index parts to be ignored.

Use LIKE 'prefix%' instead of LIKE '%pattern%' to keep the index usable.

Replace OR conditions with UNION or separate queries when possible.

When both IN and EXISTS are applicable, choose based on which sub‑query returns fewer rows.

Order‑by fields should match the index order to avoid using filesort.

Index coverage ( using index) eliminates the need to read the base table.

Practical Examples

Various CREATE TABLE and INSERT statements illustrate how to set up test data, create indexes, and observe the impact on EXPLAIN output for single‑table, two‑table, and three‑table queries.

Key takeaways include:

Small tables should drive large tables in joins; index the join column on the smaller table.

Composite indexes must be defined in the same order as they are used in WHERE clauses.

When an IN list appears, placing it at the end of the condition list can improve index usage.

Using EXPLAIN after each change confirms whether the optimizer is using the intended index.

Conclusion

Effective MySQL performance tuning relies on understanding the server’s execution flow, designing appropriate indexes, and continuously verifying query plans with EXPLAIN. By following the principles outlined above, developers can significantly reduce query latency and resource consumption.