EXPLAIN query analysis in MySQL

Introduction

EXPLAIN helps you understand how MySQL runs your query. It shows the steps MySQL takes to get your data.

You want to see if your query is fast or slow.

You want to find out which part of your query uses a lot of resources.

You want to check if your database uses indexes to speed up the search.

You want to improve your query to make your app faster.

You want to learn how MySQL processes your SQL commands.

Syntax

MySQL

EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;

You put EXPLAIN before your SELECT query to see its plan.

The output shows details like which indexes are used and how tables are joined.

Examples

Shows how MySQL reads all rows from the employees table.

MySQL

EXPLAIN SELECT * FROM employees;

Shows how MySQL finds employees in the Sales department.

MySQL

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

Shows how MySQL joins two tables to get employee and department names.

MySQL

EXPLAIN SELECT e.name, d.name FROM employees e JOIN departments d ON e.dept_id = d.id;

Sample Program

This query shows how MySQL finds employees earning more than 50000.

MySQL

EXPLAIN SELECT name, salary FROM employees WHERE salary > 50000;

Important Notes

The 'type' column shows how MySQL searches the table; 'ALL' means full table scan, which is slower.

If 'key' is NULL, no index is used; adding indexes can speed up queries.

The 'rows' column estimates how many rows MySQL reads to answer the query.

Summary

EXPLAIN shows the plan MySQL uses to run your query.

It helps find slow parts and improve performance.

Use EXPLAIN before SELECT to see details about indexes and joins.