MySQL - MySQL Query Execution Plan (EXPLAIN & ANALYZE)

 

Understanding how MySQL executes a query is essential for writing efficient and high-performance database operations. Even a correctly written query can perform poorly if MySQL chooses an inefficient execution strategy. This is where tools like EXPLAIN and EXPLAIN ANALYZE become important, as they help developers inspect and optimize query behavior.

What is a Query Execution Plan

A query execution plan is a detailed roadmap that shows how MySQL processes a SQL query. Instead of executing a query blindly, MySQL evaluates different strategies and selects what it considers the most efficient path. This includes decisions such as which indexes to use, the order of table access, and how joins are performed.

The execution plan helps answer questions like:

  • Is MySQL scanning the entire table or using an index?

  • In what order are tables being joined?

  • How many rows are being examined?

  • What type of join method is being used?

Understanding EXPLAIN

The EXPLAIN keyword is used before a SELECT (or other supported queries like UPDATE and DELETE) to display the execution plan without actually running the query.

Example:

EXPLAIN SELECT * FROM orders WHERE customer_id = 10;

This command returns a table with several columns that describe how MySQL will execute the query.

Key columns include:

  • id: Identifies the query step. Higher values may indicate subqueries or nested operations.

  • select_type: Shows the type of SELECT query, such as SIMPLE, PRIMARY, SUBQUERY, or DERIVED.

  • table: The table being accessed in that step.

  • type: Indicates how rows are accessed. This is one of the most important columns.

    • ALL: Full table scan (least efficient)

    • index: Full index scan

    • range: Index range scan

    • ref: Index lookup using a non-unique key

    • eq_ref: Index lookup using a unique key (very efficient)

    • const: Single row match (best case)

  • possible_keys: Indexes that MySQL could use.

  • key: The actual index chosen.

  • rows: Estimated number of rows MySQL will examine.

  • Extra: Additional details such as "Using where", "Using index", or "Using temporary".

Understanding EXPLAIN ANALYZE

While EXPLAIN shows the estimated execution plan, EXPLAIN ANALYZE goes a step further by actually executing the query and providing real performance metrics.

Example:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 10;

This provides:

  • Actual execution time for each step

  • Actual number of rows processed

  • Comparison between estimated and actual values

This is extremely useful because sometimes MySQL’s estimates are not accurate, especially when table statistics are outdated.

Types of Access Methods

The “type” column in EXPLAIN is critical for performance analysis. It shows how MySQL accesses data:

  • Full Table Scan (ALL): MySQL scans every row. This is slow for large tables.

  • Index Scan (index): Scans the entire index instead of the table.

  • Range Scan (range): Uses an index to scan a range of values.

  • Index Lookup (ref, eq_ref): Efficient lookups using indexes.

  • Constant Lookup (const): Fastest, when a single row is matched.

Improving query performance often involves moving from ALL to a more efficient type like ref or eq_ref.

Join Execution Order

When multiple tables are involved, MySQL decides the order in which tables are joined. The execution plan lists tables in the order they are processed. Poor join order can significantly slow down queries.

Using EXPLAIN, you can identify inefficient joins and restructure queries or add indexes to improve performance.

Common Performance Issues Identified

By analyzing execution plans, you can detect:

  • Missing indexes, leading to full table scans

  • Inefficient joins causing large intermediate results

  • Use of temporary tables or filesort operations

  • High row examination counts compared to returned rows

For example, if EXPLAIN shows "Using filesort", it indicates MySQL is performing an extra sorting step, which can be expensive.

Optimizing Queries Using Execution Plans

Once you understand the execution plan, you can optimize queries by:

  • Adding appropriate indexes to reduce scanning

  • Rewriting queries to avoid unnecessary complexity

  • Reducing the number of rows processed

  • Ensuring proper filtering conditions

  • Avoiding SELECT * when only specific columns are needed

You can repeatedly use EXPLAIN after making changes to verify improvements.

Difference Between EXPLAIN and EXPLAIN ANALYZE

EXPLAIN provides an estimated plan without executing the query, making it fast and safe for analysis. EXPLAIN ANALYZE executes the query and gives actual runtime statistics, making it more accurate but slightly heavier.

In practice, EXPLAIN is used during initial debugging, while EXPLAIN ANALYZE is used for deeper performance tuning.

Conclusion

The MySQL query execution plan is a powerful tool for understanding how queries run internally. By mastering EXPLAIN and EXPLAIN ANALYZE, developers can identify inefficiencies, reduce execution time, and build scalable database systems. Instead of guessing performance issues, execution plans provide a clear, data-driven approach to query optimization.