MySQL DESCRIBE

MySQL DESCRIBE command provides information about how a particular table is constructed or how a particular query is executed. EXPLAIN and DESC are synonyms of DESCRIBE in MySQL and these three commands work the same way in all aspects.

In this article, we will look at examples to learn the usage of MySQL DESCRIBE to explain table structures and query executions.

MySQL DESCRIBE table – Syntax

Given below is the syntax to explain table structures using MySQL DESCRIBE command:

1. EXPLAIN | DESCRIBE | DESC
3. table_name
5. [column_name | wild]

MySQL Describe table – Syntax

Here, column_name is optional and if supplied DESCRIBE command will provide information only about that particular column of the table. Instead of mentioning exact column_name, we can also mention strings with ‘%’ to match the column_name through a wild search.

MySQL DESCRIBE query – Syntax

The syntax to explain query execution using MySQL DESCRIBE command is shown below:

1. EXPLAIN | DESCRIBE | DESC
3. [FORMAT = type]
5. statement

MySQL Describe query – Syntax

The SQL statement, whose execution plan is required has to be mentioned in place of “statement”. The “FORMAT” part is optional and it can be used to modify the format of output fetched. “TRADITIONAL”, “JSON” and “TREE” are three available options, which can be used here in place of “type”.

Example

Let’s say we have two tables: “Organisation” and “OrganisationBranchMap” with the following structure.

1. CREATE TABLE Organisation
3. (
5. ID INT PRIMARY KEY,
7. Name VARCHAR(100)
9. );
11. CREATE TABLE OrganisationBranchMap
13. (
15. ID INT PRIMARY KEY,
17. Name VARCHAR(100),
19. Address VARCHAR(300),
21. Organisation_ID INT,
23. FOREIGN KEY (Organisation_ID) REFERENCES Organisation(ID)
25. );
27. INSERT INTO Organisation(ID,Name) VALUES
29. (1,'AAA'),
31. (2,'BBB'),
33. (3,'CCC');
35. INSERT INTO OrganisationBranchMap(ID,Name, Address, Organisation_ID) VALUES
37. (1,'123','70 Well Lane, PARTON',1),
39. (2,'456','96 Newmarket Road, HAWTHORN HILL', 1),
41. (3,'689','81 Cunnery Rd, MALLWD',2),
43. (4,'246','87 Shannon Way, CHICKLADE',3);

Now, we query the structure of “OrganisationBranchMap” table using MySQL DESCRIBE command.

1. DESCRIBE
3. OrganisationBranchMap

MySQL Describe “OrganisationBranchMap”

Result:

“OrganisationBranchMap” table structure – all columns

We can also query only for columns with its name including ‘ID’ in it by executing the following query:

1. DESCRIBE
3. OrganisationBranchMap
5. ‘%ID%’

MySQL Describe “OrganisationBranchMap” column_names with ‘%ID%’

Result:

“OrganisationBranchMap” table structure – column_names with ‘%ID%’

As the next step, we will query for execution plan of a SELECT statement in JSON format as given below:

1. EXPLAIN
3. FORMAT= JSON
5. SELECT
7. ob.ID,ob.Name,ob.Address,o.Name
9. FROM
11. OrganisationBranchMap ob
13. INNER JOIN
15. Organisation o
17. ON
19. ob.Organisation_ID = o.ID;

MySQL Describe “SELECT” statement

Result:

{\n "query_block": {\n "select_id": 1,\n "cost_info": {\n "query_cost": "7.07"\n },\n "nested_loop": [\n {\n "table": {\n "table_name": "o",\n "access_type": "ALL",\n "possible_keys": [\n "PRIMARY"\n ],\n "rows_examined_per_scan": 3,\n "rows_produced_per_join": 3,\n "filtered": "100.00",\n "cost_info": {\n "read_cost": "2.01",\n "eval_cost": "0.60",\n "prefix_cost": "2.61",\n "data_read_per_join": "1K"\n },\n "used_columns": [\n "ID",\n "Name"\n ]\n }\n },\n {\n "table": {\n "table_name": "ob",\n "access_type": "ALL",\n "possible_keys": [\n "Organisation_ID"\n ],\n "rows_examined_per_scan": 4,\n "rows_produced_per_join": 3,\n "filtered": "25.00",\n "using_join_buffer": "Block Nested Loop",\n "cost_info": {\n "read_cost": "2.05",\n "eval_cost": "0.60",\n "prefix_cost": "7.07",\n "data_read_per_join": "4K"\n },\n "used_columns": [\n "ID",\n "Name",\n "Address",\n "Organisation_ID"\n ],\n "attached_condition": "(`test`.`ob`.`Organisation_ID` = `test`.`o`.`ID`)"\n }\n }\n ]\n }\n}

MySQL Describe “SELECT” statement output in JSON format

From this, we will be able to understand the steps involved in the execution of the “SELECT” statement and the query cost related information at each step.

Describe the column structure of a MySQL table

The SQL command ‘describe’ allows you to view the column structure of a MySQL table. It provides a listing of the columns of a table, the type of each column, and additional information on each column.

To view the structure of a MySQL table called ‘recipes’ use the following SQL command:

describe recipes;

The command can be issued from any SQL source including an application with database connectivity (i.e., PHP, Java, etc.) or directly from mysql command (see Connect to a MySQL server using the mysql command for more information).

Get a MySQL table structure with DESCRIBE

There are at least two ways to get a MySQL table's structure using SQL queries. The first is using DESCRIBE and the second by querying the INFORMATION_SCHEMA. This post deals with the DESCRIBE function and the next MySQL postlooks at the INFORMATION_SCHEMA.

Example table

The example table used in this post was created with the following SQL:

CREATE TABLE `products` (
`product_id` int(10) unsigned NOT NULL auto_increment,
`url` varchar(100) NOT NULL,
`name` varchar(50) NOT NULL,
`description` varchar(255) NOT NULL,
`price` decimal(10,2) NOT NULL,
`visible` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`product_id`),
UNIQUE KEY `url` (`url`),
KEY `visible` (`visible`)
)

Using DESCRIBE

The SQL query to get the table structure is:

DESCRIBE products;

You can run this from the MySQL CLI; phpMyAdmin; or using a programming language like PHP and then using the functions to retrieve each row from the query.

The resulting data from the MySQL CLI looks like this for the example table above:

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| product_id  | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| url         | varchar(100)        | NO   | UNI | NULL    |                |
| name        | varchar(50)         | NO   |     | NULL    |                |
| description | varchar(255)        | NO   |     | NULL    |                |
| price       | decimal(10,2)       | NO   |     | NULL    |                |
| visible     | tinyint(1) unsigned | NO   | MUL | 1       |                |
+-------------+---------------------+------+-----+---------+----------------+

Using PHP

If you were using PHP, for example, you could do something like this:

$res = mysql_query('DESCRIBE products');
while($row = mysql_fetch_array($res)) {
    echo "{$row['Field']} - {$row['Type']}\n";
}

The output using the example table would be this:

product_id - int(10) unsigned
url - varchar(100)
name - varchar(50)
description - varchar(255)
price - decimal(10,2)
visible - tinyint(1) unsigned

Querying the INFORMATION_SCHEMA

My next MySQL post will look at how to do the same using the INFORMATION_SCHEMA, which provides more information than DESCRIBE, although I have found querying the INFORMATION_SCHEMA can run a little slowly sometimes myself.

Related posts:

• MySQL: SQL to drop a column from a table
• Get a MySQL table structure from the INFORMATION_SCHEMA