In MySQL we can use the DESCRIBE statement to get information about table structure in the database, or to obtain a query execution plan for a query. For example, we can get information about column names, their data types, default values, etc.
The DESC and EXPLAIN statements are synonyms of DESCRIBE, so we can alternatively use those to do the same thing.
Example
Here’s a quick example to demonstrate:
DESCRIBE t1;Result:
+-------+-------------+------+-----+---------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-----------------------------+ | c1 | varchar(20) | YES | | NULL | | | c2 | varchar(20) | YES | | NULL | | | c3 | varchar(20) | YES | | NULL | VIRTUAL GENERATED INVISIBLE | +-------+-------------+------+-----+---------+-----------------------------+
Here I used DESCRIBE to get information about a table called t1. We can see that this table contains three columns called c1, c2, and c3, and we get certain information about those columns.
The Extra column provides extra information. We can see that the c3 column is a generated column. We can also see that it’s an invisible column.
Specifying the Column Name
We have the option of specifying a column name within the table. When we do this, we only get information about that specific column:
DESCRIBE t1 c3;Result:
+-------+-------------+------+-----+---------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-----------------------------+ | c3 | varchar(20) | YES | | NULL | VIRTUAL GENERATED INVISIBLE | +-------+-------------+------+-----+---------+-----------------------------+
In this example I narrowed it down to just the c3 column.
Get a Query Execution Plan
We can also use the DESCRIBE statement to get information about queries and other SQL statements (specifically the SELECT, DELETE, INSERT, REPLACE, TABLE, and UPDATE statements).
This usage is commonly assigned to the EXPLAIN statement, but as mentioned, that’s merely a synonym for DESCRIBE, so we can use them interchangeably.
Here’s an example of using DESCRIBE to get a query execution plan:
DESCRIBE SELECT * FROM t1;Result:
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
We can get additional information by checking the warnings. In this case there was just one warning. Let’s check it:
SHOW WARNINGS;Result:
+-------+------+------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `pethotel`.`t1`.`c1` AS `c1`,`pethotel`.`t1`.`c2` AS `c2` from `pethotel`.`t1` | +-------+------+------------------------------------------------------------------------------------------------------+
This shows us a query that explicitly names each qualified column that was selected by our query. Note that the third column (c3) isn’t included here. That’s because we used the asterisk wildcard (*) in our query to select all columns. When we do this, invisible columns are not included. The c3 column in our table is an invisible column, as we saw in the first example when we used DESCRIBE to get the table information.
Specify the Format
We can use the FORMAT argument to specify the output format. As of this writing, the valid formats are TRADITIONAL, JSON, and TREE.
Here’s an example of setting the format to JSON:
DESCRIBE FORMAT=JSON SELECT * FROM t1;Result:
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "0.95"
},
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows_examined_per_scan": 7,
"rows_produced_per_join": 7,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.70",
"prefix_cost": "0.95",
"data_read_per_join": "1K"
},
"used_columns": [
"c1",
"c2"
]
}
}
}
The default format is TRADITIONAL, but this will depend on your explain_format system variable if you have it (this system variable was introduced in MySQL 8.0.32). If your explain_format variable is set to a value other than TRADITIONAL, then the default format will be whatever that value is (assuming it’s a valid value).
We can check the value of the explain_format system variable like this:
SELECT @@explain_format;Result:
+------------------+ | @@explain_format | +------------------+ | TRADITIONAL | +------------------+
In my case it’s set to the default value of TRADITIONAL.
The DESC Statement
As mentioned DESC is a synonym for DESCRIBE. Therefore we can replace DESCRIBE with DESC to get the same output.
Example:
DESC t1;Result:
+-------+-------------+------+-----+---------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-----------------------------+ | c1 | varchar(20) | YES | | NULL | | | c2 | varchar(20) | YES | | NULL | | | c3 | varchar(20) | YES | | NULL | VIRTUAL GENERATED INVISIBLE | +-------+-------------+------+-----+---------+-----------------------------+
We got the same result that we got when using DESCRIBE.
DESCRIBE for a Certain Connection
We can use DESCRIBE to get the execution plan for the statement being executed at that connection.
For example:
DESCRIBE FOR CONNECTION 128;That assumes there’s a connection with a connection ID of 128, and that it’s executing a statement.
If we specify a connection ID that doesn’t exist, we’ll get an “unknown thread” error:
ERROR 1094 (HY000): Unknown thread id: 128
We can use the following command to get a list of connections:
SHOW PROCESSLIST;DESCRIBE for a Certain Database
As from MySQL 8.3, we can use FOR SCHEMA or its synonym FOR DATABASE to specify a database to apply the DESCRIBE statement to.
For example:
DESCRIBE FOR DATABASE sakila
SELECT * FROM actor;That example applies the given SELECT against the sakila database, even though we could be in a different database.
In this case I used FOR DATABASE, which is a synonym of FOR SCHEMA.
About DESCRIBE ANALYZE
From MySQL 8.0.18 we can use DESCRIBE ANALYZE to get DESCRIBE output along with timing and additional, iterator-based, information about how the optimiser’s expectations matched the actual execution.
We can use DESCRIBE ANALYZE on SELECT and TABLE statements, as well as with multi-table UPDATE and DELETE statements.
Example:
DESCRIBE ANALYZE
SELECT * FROM actor a
JOIN film_actor fa
ON a.actor_id = fa.actor_id
JOIN film f
ON f.film_id = fa.film_id
WHERE last_name LIKE 'Pit%';Result:
-> Nested loop inner join (cost=15.8 rows=27.3) (actual time=0.105..0.343 rows=31 loops=1)
-> Nested loop inner join (cost=4.39 rows=27.3) (actual time=0.0836..0.115 rows=31 loops=1)
-> Index range scan on a using idx_actor_last_name over ('Pit' <= last_name <= 'Pit????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????'), with index condition: (a.last_name like 'Pit%') (cost=0.71 rows=1) (actual time=0.0454..0.0496 rows=1 loops=1)
-> Index lookup on fa using PRIMARY (actor_id=a.actor_id) (cost=3.68 rows=27.3) (actual time=0.0367..0.0578 rows=31 loops=1)
-> Single-row index lookup on f using PRIMARY (film_id=fa.film_id) (cost=0.322 rows=1) (actual time=0.00683..0.00691 rows=1 loops=31)
The DESCRIBE ANALYZE statement only supports the TREE format. Each node represents an iterator. Each iterator outputs the estimated execution cost, the estimated number of returned rows, the time to return first row, time spent (in milliseconds) executing this iterator (including child iterators, but not parent iterators), and the number of rows returned by the iterator.
As of MySQL 8.0.32, if the explain_format system variable (which was introduced in MySQL 8.0.32) is set to TRADITIONAL, DEFAULT or TREE, then TREE is used. If the value is JSON, then an error is returned, unless FORMAT=TREE is specified as part of the statement.
I didn’t get an error in the above example because my explain_format system variable is set to TRADITIONAL. Therefore, it used TREE. But if my explain_format variable was set to JSON, then I would’ve received an error.
Privileges
DESCRIBE requires the same privileges required to execute the explained statement. It also requires the SHOW VIEW privilege for any explained view.
Also DESCRIBE … FOR CONNECTION requires the PROCESS privilege if the specified connection belongs to a different user.
More Information
See the MySQL documentation for EXPLAIN, which provides more information about the DESCRIBE / DESC / EXPLAIN statement.