How to use DESCRIBE and EXPLAIN in MySQL?

In MySQL, the DESCRIBE and EXPLAIN statements are synonyms, used either to obtain information about table structure or query execution plans.

1. To describe a table:

Even though DESCRIBE and EXPLAIN statements are synonyms, the DESCRIBE statement is used more to obtain information about a table structure while EXPLAIN statement is used to obtain a query execution plan.

The DESCRIBE statement is a shortcut for SHOW COLUMN statement:

DESCRIBE table_name;

is equivalent to this SHOW COLUMN statement:

SHOW COLUMNS FROM table_name;

Or you can also use the short form of describe:

DESC table_name;

Those describe statements above show the columns in the table and all their attributes such as name, data type, collation, Nullability, Primary key, default, comment, etc.

Instead of DESCRIBE or DESC, you can use EXPLAIN statement which works the same:

EXPLAIN table_name;

2. To describe a query execution plan

We often use EXPLAIN. It provides information about how your SQL database executes a query.

EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. It also requires the SELECT privilege for any tables or views accessed, including any underlying tables of views. For views, EXPLAIN also requires the SHOW VIEW privilege.

EXPLAIN SELECT * FROM table_name;

In practice, the EXPLAIN statement is used for query optimization. When you have a slow query, it can show you where you should add indexes to speed up, or to check whether the optimizer joins the tables in an optimal order.

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. 