EXPLAIN plan for query analysis in SQL

Introduction

EXPLAIN helps you see how the database will run your query. It shows the steps and order the database uses to get your data.

You want to make your query faster by understanding how it runs.

You need to find out why a query is slow.

You want to check if the database uses indexes for your query.

You are learning how databases process queries step-by-step.

Syntax

SQL

EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;

EXPLAIN shows the query plan without running the query fully.

The output format can vary slightly between database systems.

Examples

Shows the plan for selecting all rows and columns from the employees table.

SQL

EXPLAIN SELECT * FROM employees;

Shows how the database finds customers in Paris.

SQL

EXPLAIN SELECT name FROM customers WHERE city = 'Paris';

Shows the plan for joining orders and customers tables.

SQL

EXPLAIN SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;

Sample Program

This query plan shows how the database will find users older than 30.

SQL

EXPLAIN SELECT name, age FROM users WHERE age > 30;

Important Notes

The output columns may include id, select_type, table, type, possible_keys, key, key_len, ref, rows, and Extra.

Look for 'Using index' or 'Using where' in Extra to understand filtering and index use.

EXPLAIN does not run the query fully, so it is safe to use on large tables.

Summary

EXPLAIN shows how the database plans to run your query.

It helps find slow parts and improve query speed.

Use EXPLAIN before optimizing queries to understand their behavior.