What is the EXPLAIN statement and how to use it in MySQL?

EXPLAIN in MySQL is used to analyze query execution plans without running the query, helping identify performance issues. 1. Use EXPLAIN before a SELECT, INSERT, UPDATE, DELETE, or REPLACE statement, most commonly with SELECT. 2. Key output columns include id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, and Extra. 3. The type column should ideally show const, eq_ref, or ref, while avoiding ALL, which indicates a full table scan. 4. Check if key is NULL despite indexes listed in possible_keys, indicating no index is used, so consider adding or modifying indexes. 5. A high value in rows suggests inefficiency; reduce it with better indexing. 6. Avoid Using temporary and Using filesort in the Extra column, as they indicate expensive operations. 7. Create composite indexes, such as (city, age), to eliminate filesorts when filtering and sorting. 8. Use EXPLAIN FORMAT=JSON for more detailed, structured insights including optimizer decisions. 9. For actual execution metrics, use EXPLAIN ANALYZE in MySQL 8.0.18 . Regular use of EXPLAIN improves query efficiency and overall database performance.

The EXPLAIN statement in MySQL is a tool that shows how the database engine executes a query. It provides insight into how MySQL uses indexes, joins tables, filters rows, and organizes data retrieval. By using EXPLAIN, you can identify performance bottlenecks and optimize slow queries.

You use EXPLAIN by placing it before a SELECT, INSERT, UPDATE, DELETE, or REPLACE statement. However, it's most commonly used with SELECT queries to analyze execution plans.

How to Use EXPLAIN

To use EXPLAIN, simply prepend it to your SQL query:

EXPLAIN SELECT * FROM users WHERE age > 30;

This will return a result set with several columns that describe how MySQL plans to execute the query.

Key Columns in EXPLAIN Output

Each row in the output corresponds to a table used in the query. Important columns include:

• id: The identifier for the query or subquery. Higher numbers may indicate subqueries or dependent queries.
• select_type: Indicates the type of query (e.g., SIMPLE, PRIMARY, SUBQUERY, DERIVED).
• table: The name of the table being accessed.
• partitions: Shows which partitions are used (if partitioning is enabled).
• type: The join type, which reflects how MySQL accesses the rows. Common values (from best to worst):
  • system / const: Only one or zero rows matched (very fast).
  • eq_ref: Primary key or unique index used in a join.
  • ref: Non-unique index used to match rows.
  • range: Index is used to retrieve a range of values.
  • index: Full index scan (slower than range).
  • ALL: Full table scan (usually a performance red flag).
• possible_keys: Lists indexes MySQL could use for this query.
• key: The actual index chosen by the optimizer.
• key_len: Length of the key used; shorter is generally better.
• ref: Shows which column or constant is used with the key.
• rows: Estimated number of rows MySQL must examine. Lower is better.
• filtered: Percentage of rows filtered by the table condition (introduced in MySQL 5.7).
• Extra: Additional information, such as:
  • Using where: Applying a WHERE clause.
  • Using index: Index covers all needed columns (fast).
  • Using temporary: Creating a temporary table (can be slow).
  • Using filesort: Sorting data externally (avoid if possible).

Practical Tips for Using EXPLAIN

• Check the type column: Aim for const, eq_ref, or ref. Avoid ALL unless dealing with very small tables.
• Look at key and possible_keys: If key is NULL, MySQL isn’t using an index. Consider adding or adjusting indexes.
• Watch the rows estimate: A high number suggests inefficiency. Try to reduce it with better indexing or query structure.
• Minimize Using temporary and Using filesort: These operations are expensive. Optimize queries with proper indexing and avoid unnecessary sorting or grouping.

For example, if you see:

EXPLAIN SELECT name FROM users WHERE city = 'New York' ORDER BY age;

And the output shows Using filesort, you might create a composite index on (city, age) to avoid sorting.

EXPLAIN vs. EXPLAIN FORMAT=JSON

MySQL also supports EXPLAIN FORMAT=JSON, which gives a more detailed, structured output including cost estimates and optimizer decisions:

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 30;

This is useful for deeper analysis, especially in complex queries.

Final Notes

EXPLAIN doesn’t run the query—it only shows the execution plan. To see actual performance, consider EXPLAIN ANALYZE (available in MySQL 8.0.18 ), which executes the query and shows real timing and row counts.

Using EXPLAIN regularly helps write efficient queries and maintain good database performance. It’s a must-have tool for developers and DBAs working with MySQL.

The above is the detailed content of What is the EXPLAIN statement and how to use it in MySQL?. For more information, please follow other related articles on the PHP Chinese website!