Table of Contents
How to Use EXPLAIN
Understanding the Output Columns
Using EXPLAIN with JOINs and Complex Queries
EXPLAIN FORMAT Options
EXPLAIN vs EXPLAIN ANALYZE
Home Database Mysql Tutorial How to use the EXPLAIN command in MySQL?

How to use the EXPLAIN command in MySQL?

Sep 18, 2025 am 01:48 AM
mysql explain

EXPLAIN in MySQL reveals query execution plans, showing index usage, table read order, and row filtering to optimize performance; use it before SELECT to analyze steps, check key columns like type and rows, identify inefficiencies in Extra, and combine with indexing strategies for faster queries.

How to use the EXPLAIN command in MySQL?

The EXPLAIN command in MySQL helps you understand how the database executes a query. It shows the execution plan, including which indexes are used, the order of table reads, and how rows are filtered. This is essential for optimizing slow queries.

How to Use EXPLAIN

Simply add EXPLAIN before your SELECT (or other supported) statement:

EXPLAIN SELECT * FROM users WHERE id = 1;

This returns a result set with information about how MySQL would execute the query, not the actual query results.

Understanding the Output Columns

Each row in the EXPLAIN output represents a step in the query execution. Key columns include:

  • id: The query's unique identifier. Values may repeat if there are subqueries or unions.
  • select_type: Type of the SELECT (e.g., SIMPLE, PRIMARY, SUBQUERY).
  • table: The table being accessed.
  • partitions: Which partitions are matched (if partitioning is used).
  • type: Join type. Important for performance: system, const, eq_ref, ref, range, index, ALL (ALL is usually bad).
  • possible_keys: Indexes MySQL could use.
  • key: The actual index used.
  • key_len: Length of the key used. Helps determine if full or partial index is used.
  • ref: Shows which column or constant is compared to the index.
  • rows: Estimated number of rows MySQL must examine.
  • filtered: Percentage of rows filtered by table condition.
  • Extra: Additional info like "Using where", "Using index", "Using temporary", "Using filesort". These can indicate inefficiencies.

Using EXPLAIN with JOINs and Complex Queries

For queries involving multiple tables, EXPLAIN shows one row per table access. The order indicates join sequence. Look at:

  • Whether type is efficient (avoid ALL when possible).
  • If proper indexes are used (key column).
  • High rows values, which suggest full table scans.
  • Warnings in Extra, like "Using temporary" (temporary table created) or "Using filesort" (expensive sort operation).

Example:

EXPLAIN SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 'active';

EXPLAIN FORMAT Options

You can request different output formats:

  • EXPLAIN FORMAT=TRADITIONAL: Default tabular format.
  • EXPLAIN FORMAT=JSON: Provides detailed structured info, including cost estimates and used indexes.

JSON format is helpful for deeper analysis:

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 30;

EXPLAIN vs EXPLAIN ANALYZE

In MySQL 8.0 , EXPLAIN ANALYZE runs the query and shows actual execution statistics, comparing them to estimates. This gives real insight into performance issues:

EXPLAIN ANALYZE SELECT * FROM posts WHERE author_id = 5;

It displays actual runtime, loops, and row counts—very useful for tuning.

Basically, use EXPLAIN whenever you're checking query performance. It’s a fast way to spot full table scans, missing indexes, or inefficient joins. Combine it with proper indexing and query restructuring to improve speed.

The above is the detailed content of How to use the EXPLAIN command in MySQL?. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undress AI Tool

Undress AI Tool

Undress images for free

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

ArtGPT

ArtGPT

AI image generator for creative art from text prompts.

Stock Market GPT

Stock Market GPT

AI powered investment research for smarter decisions

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

PHP Tutorial
1679
276
How to use a CASE statement in MySQL? How to use a CASE statement in MySQL? Sep 20, 2025 am 02:00 AM

The answer is: MySQL's CASE statement is used to implement conditional logic in query, and supports two forms: simple and search. Different values ​​can be dynamically returned in clauses such as SELECT, WHERE, and ORDERBY; for example, in SELECT, classification of scores by fractional segments, combining aggregate functions to count the number of states, or prioritizing specific roles in ORDERBY, it is necessary to always end with END and it is recommended to use ELSE to handle the default situation.

How to Automate MySQL Backups with a Script? How to Automate MySQL Backups with a Script? Sep 21, 2025 am 02:24 AM

Create a shell script containing the database configuration and mysqldump command and save it as mysql_backup.sh; 2. Store MySQL credentials by creating ~/.my.cnf file and set 600 permissions to improve security, modify the script to use configuration file authentication; 3. Use chmod x to make the script executable and manually test whether the backup is successful; 4. Add timed tasks through crontab-e, such as 02/path/to/mysql_backup.sh>>/path/to/backup/backup.log2>&1, realize automatic backup and logging at 2 a.m. every day; 5.

How to use subqueries in MySQL? How to use subqueries in MySQL? Sep 20, 2025 am 01:07 AM

Subqueries can be used in WHERE, FROM, SELECT, and HAVING clauses to implement filtering or calculation based on the result of another query. Operators such as IN, ANY, ALL are commonly used in WHERE; alias are required as derivative tables in FROM; single values ​​must be returned in SELECT; related subqueries rely on outer query to execute each row. For example, check employees whose average salary is higher than the department, or add the company average salary list. Subqueries improve logical clarity, but performance may be lower than JOIN, so you need to ensure that you return the expected results.

How to update a row if it exists or insert if not in MySQL How to update a row if it exists or insert if not in MySQL Sep 21, 2025 am 01:45 AM

INSERT...ONDUPLICATEKEYUPDATE implementation will be updated if it exists, otherwise it will be inserted, and it requires unique or primary key constraints; 2. Reinsert after deletion of REPLACEINTO, which may cause changes in the auto-increment ID; 3. INSERTIGNORE only inserts and does not repetitive data, and does not update. It is recommended to use the first implementation of upsert.

How to use the EXPLAIN command in MySQL? How to use the EXPLAIN command in MySQL? Sep 18, 2025 am 01:48 AM

EXPLAINinMySQLrevealsqueryexecutionplans,showingindexusage,tablereadorder,androwfilteringtooptimizeperformance;useitbeforeSELECTtoanalyzesteps,checkkeycolumnsliketypeandrows,identifyinefficienciesinExtra,andcombinewithindexingstrategiesforfasterqueri

How to select distinct values in MySQL? How to select distinct values in MySQL? Sep 16, 2025 am 12:52 AM

Use the DISTINCT keyword to remove duplicate values ​​from the specified column and return unique values. 1. The basic syntax is SELECTDISTINCTcolumn_nameFROMtable_name; 2. Query the unique value of a single column, such as SELECTDISTINCTcityFROMcustomers; 3. Query the unique combination of multiple columns, such as SELECTDISTINCTcity, stateFROMcustomers; 4. Filter with the WHERE clause and get the unique value, such as SELECTDISTINCTproduct_nameFROMordersWHEREorder_date>'202

How to calculate distance between two points in MySQL How to calculate distance between two points in MySQL Sep 21, 2025 am 02:15 AM

MySQL can calculate geographical distances through the Haversine formula or the ST_Distance_Sphere function. The former is suitable for all versions, and the latter provides easier and more accurate spherical distance calculations since 5.7.

How to handle timezones in MySQL? How to handle timezones in MySQL? Sep 20, 2025 am 04:37 AM

Use UTC to store time, set the MySQL server time zone to UTC, use TIMESTAMP to realize automatic time zone conversion, adjust the time zone according to user needs in the session, display the local time through the CONVERT_TZ function, and ensure that the time zone table is loaded.

See all articles