How to use DESCRIBE and EXPLAIN in MySQL?
https://tableplus.com/blog/2018/11/mysql-describe-explain-details-table-structure-query.html
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:
is equivalent to this SHOW COLUMN statement:
Or you can also use the short form of describe:
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:
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.
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.
Download TablePlus for Mac. It’s free anyway!
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS.
Last updated
