To understand why MySQL query is slow, you must first use the EXPLAIN statement to analyze the query execution plan. 1. EXPLAIN displays the execution steps of the query, including the accessed table, join type, index usage, etc.; 2. Key columns such as type (connection type), possible_keys and key (index selection), rows (scanned rows), and Extra (extra information) help identify performance bottlenecks; 3. When using EXPLAIN, you should prioritize checking queries in the slow query log to observe whether there are full table scans (type: ALL) or high rows values; 4. Pay attention to the prompts such as "Using filesort" or "Using temporary" in the Extra column, which may mean that the sorting or grouping operations need to be optimized; 5. For complex queries, you can use EXPLAIN FORMAT=JSON to obtain more detailed information. Through these steps, we can effectively identify and optimize missing indexes, wrong connection order, unnecessary sorting and other problems, thereby improving query performance.

When you're trying to understand why a MySQL query is slow, one of the most powerful tools at your disposal is the EXPLAIN statement. It gives you insight into how MySQL executes your queries, helping you identify bottlenecks and optimize performance.

What EXPLAIN Shows You

Running EXPLAIN before a SELECT query returns information about how MySQL plans to execute it. This includes which tables are accessed, the join types used, whether indexes are effective, and more.

Key columns to pay attention to include:

• id : The identifier for the select query. Queries with multiple subqueries or unions will have multiple ids.
• select_type : Tells you if it's a simple SELECT, a subquery, or a UNION.
• table : Which table the row refers to.
• type : The join type—this is cruel. From best to worst: system , const , eq_ref , ref , range , index , ALL .
• possible_keys and key : Show which indexes MySQL considered and actually used.
• rows : Estimated number of rows MySQL must examine. Lower is better.
• Extra : Contains additional info like “Using filesort” or “Using temporary,” which are red flags.

Understanding these fields helps you make informed decisions about index usage and query structure.

How to Use EXPLAIN Effectively

To get the most out of EXPLAIN , follow these practices:

• Start with slow queries : Use the slow query log or tools like mysqldumpslow to find candidates.
• Run EXPLAIN on them : Add EXPLAIN before your SELECT and look for full table scans ( type: ALL ) or high rows values.
• Check the key usage : If possible_keys shows an index but key is empty, that index isn't being used. Maybe your WHERE clause isn't selective enough or uses functions on indexed columns.
• Watch the Extra column : Words like “Using filesort” or “Using temporary” suggest optimization opportunities.

For example, if you see a filesort , consider adding a covering index or reordering your ORDER BY .

You can also use EXPLAIN FORMAT=JSON for more detailed output, especially useful when analyzing complex joins or subqueries.

Common Optimization Scenarios

Here are a few real-world examples where EXPLAIN can guide you towards fixes:

• Missing Indexes :
  If type is ALL and possible_keys is NULL , you likely need an index. Try adding one on the relevant column(s) in the WHERE or JOIN clause.

• Incorrect Join Order :
  MySQL sometimes chooses a suboptimal order for joining tables. If you see large intermediate result sets early on, try restructuring the query or using STRAIGHT_JOIN .

• Unnecessary Sorting :
  If you see “Using filesort” in the Extra column, check if the sort is really needed. Sometimes adding an index that covers both the WHERE and ORDER BY clauses solves this.

• Temporary Tables in GROUP BY :
  If you see “Using temporary” along with “Using filesort” during a GROUP BY , consider optimizing the grouping columns or using derived tables.

Also, remember that EXPLAIN only shows the query plan—it doesn't execute the query. So, it won't reflect runtime issues like lock contention or disk I/O problems.

Final Notes

Using EXPLAIN regularly is a good habit when tuning queries. It's not just for fixing slow queries—it's also great for validating that your optimizations actually help. Sometimes adding an index doesn't change the plan as expected, and EXPLAIN lets you catch that early.

A common pitfall is assuming that any use of an index is good. That's not always true. An index scan ( type: index ) over many rows might be slower than a well-limited full table scan. Always check the rows value.

And don't forget: EXPLAIN works only on SELECT statements. If you want to analyze UPDATE or DELETE queries, rewrite them as SELECTs first.

Basically that's it.

The above is the detailed content of Analyzing Query Execution Plans Using EXPLAIN in MySQL. For more information, please follow other related articles on the PHP Chinese website!