Skip to content

07_EXPLAIN_Plan_Analysis.md

Latest commit

 

History

History
68 lines (44 loc) · 2.45 KB

07_EXPLAIN_Plan_Analysis.md

File metadata and controls

68 lines (44 loc) · 2.45 KB

🕵️ Topic 12.7: Query Execution Plans (EXPLAIN)

As a Senior SQL Master, you don't just "hope" your query is fast. You prove it. The EXPLAIN command allows you to look inside the MySQL Brain and see exactly how it intends to find your data.

1. Definition

EXPLAIN: A keyword you put before your SELECT statement. Instead of running the query, MySQL returns a table describing the "Execution Plan"—which indexes it will use, and how many rows it expects to read.

2. Why This Concept Exists

  • Debugging Slowness: Finding out why a query that should take 1ms is taking 5 seconds.
  • Index Verification: Confirming that MySQL is actually using the index you created.
  • Optimization: Comparing two different ways of writing the same query to see which is more efficient.

3. Key EXPLAIN Columns (PRO LEVEL)

Column What to look for
type const or ref is good. ALL is terrible (Full Table Scan).
possible_keys The indexes MySQL could use.
key The index MySQL actually selected. If this is NULL, you have no index!
rows The estimated number of rows MySQL must examine. Lower is better.
Extra Using index means it's a Covering Index (Fastest!). Using filesort is very slow.

4. Syntax / Implementation

-- Just put EXPLAIN at the start
EXPLAIN SELECT * FROM Users WHERE Email = 'test@example.com';

-- In MySQL 8.0, use ANALYZE for the actual real-time execution stats
EXPLAIN ANALYZE SELECT * FROM Users WHERE Email = 'test@example.com';

5. How to Read the Plan

  1. Look at the type column first. If you see ALL, add an index.
  2. Look at the rows. If it's close to the total number of rows in the table, your filter isn't working well.
  3. Look at Extra. If you see Using filesort, your ORDER BY is slow. Try adding an index to the sorting column.

6. Real-Life Examples

The Missing Index Catch: Query: SELECT * FROM Orders WHERE status = 'shipped' ORDER BY order_date;

  • EXPLAIN shows type: ALL and Extra: Using filesort.
  • The Fix: Create a composite index on (status, order_date).
  • Result: EXPLAIN now shows type: ref and Extra: Using index.

7. Mini Practice Tasks

  • Task 1: What does type: ALL in an EXPLAIN plan indicate?
  • Task 2: Which EXPLAIN column tells you which index MySQL actually decided to use for the query?