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.