How to Decode MySQL Execution Plans for Faster Queries

Why Execution Plans Matter

In modern software development relational databases are the primary storage tool, and most interactions happen through SQL CRUD operations. Among these, the Read operation is the most frequent, and its performance can vary dramatically depending on the query and table schema.

Using EXPLAIN to Locate Slow Queries

The most effective way to diagnose low‑performance SQL statements is to examine the execution plan with EXPLAIN. The extended version EXPLAIN EXTENDED provides additional optimization details that can be retrieved via SHOW WARNINGS. For partitioned tables, the PARTITIONS keyword can be used.

Key Columns in the Execution Plan

The plan contains several columns that describe how MySQL will execute the query:

id : numeric identifier indicating the execution order of sub‑queries; larger values execute earlier.

select_type : type of SELECT (e.g., SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION, UNION RESULT).

table : the table (or alias) referenced; <derived N> denotes a temporary table.

type : access method, ordered from best to worst: system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL. All types except ALL use an index.

possible_keys : indexes that could be used for the query.

key : the index actually chosen; NULL means no index.

key_length : length of the chosen index, calculated as (charset_factor * column_length) + null_flag + variable_flag. Example: for a VARCHAR(128) column with utf8mb4, key_length = 128*4 + 0 + 2 = 514.

ref : columns or constants used to match the index.

rows : estimated number of rows examined (not exact).

extra : additional information such as Using index, Using where, Using filesort, Using temporary.

Performance Ranking of Access Types

From best to worst: system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL. If a plan shows type=ALL, consider adding an index or rewriting the query.

Practical SQL Optimization Tips

Keep SQL statements simple; avoid deep nesting.

Use temporary tables to cache intermediate results and reduce repeated scans.

When using LIKE, avoid leading wildcards (e.g., %pattern) because they force a full‑table scan.

Avoid the != or <> operators; they disable index usage.

Replace OR conditions with UNION ALL or separate queries to preserve index usage.

Prefer BETWEEN or range conditions over IN / NOT IN, which can cause full scans.

Force index usage when necessary with FORCE INDEX(index_name) or FORCE INDEX(PRI).

Avoid functions or expressions in WHERE clauses; they prevent index utilization.

Limit large transactions to improve concurrency.

Avoid cursors when possible.

Never use SELECT *; specify only required columns.

Prefer VARCHAR / NVARCHAR over CHAR / NCHAR for variable‑length data.

Store numeric data in numeric columns rather than character columns.

Do not create excessive indexes; they slow down INSERT and UPDATE.

Indexes are ineffective when the indexed column has many duplicate values.

Illustrative Diagrams