The EXPLAIN statement in MySQL

The EXPLAIN statement in MySQL is a crucial diagnostic tool used to view the query optimizer's execution plan for a SQL statement. By prepending EXPLAIN to a query, you gain insights into how MySQL intends to process it, which is vital for performance tuning and optimization.

How to Use EXPLAIN

The basic syntax is straightforward: simply add the EXPLAIN keyword before your SELECT, INSERT, UPDATE, REPLACE, or DELETE statement.

sql

EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;

MySQL Workbench also offers a visual explain plan.

For more detailed information, especially regarding actual runtime performance in MySQL 8.0.18 and later, you can use EXPLAIN ANALYZE, which actually runs the query and provides timing information. (Note: do not run EXPLAIN ANALYZE on a production database that you don't want to modify).

sql

EXPLAIN ANALYZE SELECT column1, column2 FROM table_name WHERE condition;

Key Columns in the EXPLAIN Output

The output is presented in a table format (by default, or JSON/TREE formats are available) with several columns, each offering specific insights.

id: A unique identifier for each SELECT within the query.
select_type: The type of query part (e.g., SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION).
table: The table being accessed.
type: The join type or access method used (one of the most important columns). Values range from efficient (const, eq_ref, ref) to inefficient (index, ALL - a full table scan).
possible_keys: Indicates which indexes MySQL could potentially use. If this is NULL, it suggests a potential lack of suitable indexes.
key: The actual index MySQL decided to use.
rows: An estimate of the number of rows MySQL needs to examine to return the result. Lower is better.
filtered: An estimated percentage of rows that match the query condition. A value decreasing from 100 indicates increasing amounts of filtering after data retrieval, which might suggest a missing index for the WHERE clause.
Extra: Contains additional information about how the query is resolved. Values like Using filesort or Using temporary often indicate potential performance bottlenecks that should be optimized.

By analyzing this output, you can pinpoint inefficient operations, such as full table scans or unnecessary temporary table creation, and improve query performance by adding appropriate indexes or rewriting the query. Consult the official MySQL documentation on EXPLAIN for detailed information on all output values.