When analyzing slow queries in MySQL, the EXPLAIN command reveals how the database executes SQL statements. It shows whether indexes are used, if full table scans occur, and provides insights into the optimizer's decision-making process.

-- Find employees named Jefabc
SELECT * FROM emp WHERE name = 'Jefabc';

-- Check index usage with EXPLAIN
EXPLAIN SELECT * FROM emp WHERE name = 'Jefabc';

EXPLAIN output contains 10 columns: id, select_type, table, type, possible_keys, key, key_len, ref, rows, and Extra.

Key Columns Explained:

1. id: Query execution identifier (higher values execute first)
2. select_type: Query type (SIMPLE, PRIMARY, UNION, etc.)
3. table: Referenced table
4. type: Join type (performance from worst to best: ALL, index, range, ref, eq_ref, const, system, NULL)
5. possible_keys: Poetntial indexes MySQL could use
6. key: Actually used index
7. key_len: Index length in bytes
8. ref: Columns compared to index
9. rows: Estimated rows examined
10. Extra: Additional execution details

Join Example:

-- Find R&D employees with names starting with Jef
EXPLAIN SELECT e.id, e.name FROM employees e 
LEFT JOIN departments d ON e.dept_id = d.id 
WHERE e.name LIKE 'Jef%' AND d.name = 'R&D';

Important Notes:

• EXPLAIN doesn't show trigger, procedure, or cache effects
• Statistic are estimates, not exact values
• Only works for SELECT queries
• Doesn't reveal all optimizer transformations

Common Extra Values:

• Using where: Filtering after index retrieval
• Using temporary: Temporary table usage
• Using filesort: Exteranl sorting
• Using join buffer: No index for join
• Impossible where: No possible results