MySQL - EXPLAIN command in MySQL

1. What is EXPLAIN?

• EXPLAIN shows the execution plan for a SELECT (and sometimes DELETE, INSERT, UPDATE) query.

• It tells you how MySQL will access your tables, whether it uses indexes, how many rows it expects to scan, and in what order.

• Think of it like an "X-ray" of your query.

2. Basic Syntax

EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

or (from MySQL 8.0+)

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Sales';

• EXPLAIN → shows the planned execution.

• EXPLAIN ANALYZE → actually runs the query and shows real execution details.

3. Important Columns in EXPLAIN Output

Example output (simplified):

Key columns explained:

• id → Query step identifier (1 means first, higher values mean subqueries or derived tables).

• select_type → Type of query (SIMPLE, PRIMARY, SUBQUERY, UNION, etc.).

• table → Which table is being accessed.

• type → How rows are accessed (VERY important for performance). Common values:

  • ALL → Full table scan (worst).

  • index → Full index scan.

  • range → Range scan (e.g., BETWEEN, <, >).

  • ref → Lookup using a non-unique index.

  • eq_ref → Lookup using a unique index/primary key.

  • const/system → Single row lookup (best).

• possible_keys → Indexes that might be used.

• key → The index actually chosen.

• rows → Estimated number of rows scanned.

• Extra → Additional info (e.g., Using where, Using index, Using temporary, Using filesort).

4. Example

Let’s say you have:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    INDEX (department)
);

EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

Output might be: