MySql - Optimizing queries using EXPLAIN keyword

Optimizing Queries Using EXPLAIN Keyword in MySQL

Efficient SQL queries are the backbone of high-performing MySQL databases. As the volume of data and the complexity of operations increase, optimizing queries becomes crucial for performance. MySQL provides the EXPLAIN keyword to help developers analyze and optimize their SQL statements. This detailed guide will explore how to use EXPLAIN for query optimization, understanding the output, and applying best practices.

Introduction to EXPLAIN Keyword

The EXPLAIN keyword in MySQL is used to obtain information about how MySQL executes a query. It shows the execution plan, including table access methods, index usage, join types, and more. This helps database administrators (DBAs) and developers identify potential bottlenecks and inefficiencies in their queries.

Basic Syntax of EXPLAIN

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

This will return a row-by-row breakdown of how the query is executed internally by MySQL.

Understanding EXPLAIN Output

The output of the EXPLAIN command is tabular and includes several important columns. Each column gives insights into specific aspects of the query execution plan.

Key Columns in EXPLAIN Output

• id - The sequence number of the query in the execution plan. A higher number indicates higher precedence.
• select_type - Describes the type of SELECT being executed (e.g., SIMPLE, PRIMARY, SUBQUERY).
• table - The name of the table being accessed.
• type - The join type. This is one of the most important columns for performance tuning.
• possible_keys - Shows the indexes MySQL could consider using for this query.
• key - The actual index MySQL chose to use.
• key_len - The length of the key used.
• ref - The column used with the key to find rows.
• rows - Estimated number of rows MySQL needs to examine.
• Extra - Additional information about the query execution.

Common select_type Values

• SIMPLE: A basic SELECT query without subqueries or unions.
• PRIMARY: The outermost SELECT query.
• SUBQUERY: The first SELECT in a subquery.
• DERIVED: A subquery in the FROM clause (a derived table).
• UNION: Second or later SELECT in a UNION.

Important Join Types in type Column

The type column in the EXPLAIN output shows the join type, which has a direct impact on performance:

• ALL: Full table scan (worst case).
• index: Full index scan.
• range: Index range scan.
• ref: Non-unique index lookup.
• eq_ref: Unique index lookup (ideal case).
• const / system: Single row matched, very fast.

Interpreting the Extra Column

This column provides additional notes about the execution plan. Common values include:

• Using where: WHERE clause is being used to filter rows.
• Using index: Index-only scan, more efficient.
• Using temporary: A temporary table is being used (often bad for performance).
• Using filesort: A filesort is being done for ORDER BY (can be costly).

Example: Optimizing a Query with EXPLAIN

Original Query

SELECT * FROM orders WHERE customer_id = 1001;

EXPLAIN Output

id: 1
select_type: SIMPLE
table: orders
type: ALL
possible_keys: customer_id_idx
key: NULL
rows: 25000
Extra: Using where

Analysis: MySQL is performing a full table scan because it’s not using the index. This is inefficient for large tables.

Optimization

CREATE INDEX customer_id_idx ON orders(customer_id);

Re-run EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;

id: 1
select_type: SIMPLE
table: orders
type: ref
possible_keys: customer_id_idx
key: customer_id_idx
rows: 10
Extra: Using where

Result: The query now uses an index and scans fewer rows.

Using EXPLAIN with JOINs

EXPLAIN is especially useful when optimizing JOIN queries, which can be more complex.

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.location = 'New York';

EXPLAIN will show how tables are joined, in which order, and whether indexes are used.

Bad JOIN Example Output

table: departments
type: ALL
key: NULL
rows: 100
Extra: Using where

table: employees
type: ALL
key: NULL
rows: 10000
Extra: Using where; Using join buffer

Fix: Ensure indexes exist on department_id and location.

CREATE INDEX idx_department_id ON employees(department_id);
CREATE INDEX idx_location ON departments(location);

Using EXPLAIN EXTENDED and SHOW WARNINGS

Adding EXTENDED provides more information about how MySQL rewrites queries internally:

EXPLAIN EXTENDED SELECT * FROM employees WHERE department_id = 10;

Then use:

SHOW WARNINGS;

This will show the optimized query after parsing and rewriting. It helps understand how indexes and filters are applied.

Using EXPLAIN FORMAT=JSON

MySQL also supports a JSON format for EXPLAIN, providing a more detailed hierarchical structure.

EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE department_id = 10;

This output is more descriptive and suitable for programmatic parsing or GUI-based tools.

Limitations of EXPLAIN

While EXPLAIN is a powerful tool, it does have limitations:

• It shows estimates, not actual execution time or rows processed.
• Does not account for caching or query optimization in the query cache.
• Cannot analyze stored procedures or dynamic SQL directly.

Complementary Tools and Commands

• SHOW PROFILE - Breaks down query execution into stages.
• SHOW STATUS - Provides server performance metrics.
• Performance Schema - MySQL’s in-depth monitoring feature.

Optimizing SQL queries using the EXPLAIN keyword is one of the most effective techniques for enhancing MySQL performance. By understanding the execution plan, developers can detect performance bottlenecks, inefficient joins, and improper index usage. This leads to faster response times, reduced resource consumption, and scalable applications. EXPLAIN should be a routine part of query development and review for any database-intensive application.