MySQL - Describe Tables

MySQL: Describing Table Structure and Analyzing Queries

Understanding your database schema is fundamental for any developer. MySQL provides several commands to inspect the structure of your tables. Additionally, a related but distinct command, EXPLAIN, is crucial for analyzing how MySQL executes your queries.

This tutorial covers both aspects: how to describe a table’s columns and how to get insights into query performance.

Part 1: Describing Table Structure

To retrieve the definition of a table—its columns, data types, keys, and other attributes—you can use DESCRIBE, its shortcut DESC, or SHOW COLUMNS.

The DESCRIBE and DESC Statements

The DESCRIBE statement (and its shorter alias DESC) is the most common way to quickly see a table’s layout.

Syntax

DESCRIBE table_name;
-- or the shortcut --
DESC table_name;

Example

First, let’s create a sample CUSTOMERS table.

CREATE TABLE CUSTOMERS(
    ID INT AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    EMAIL VARCHAR(100) UNIQUE NOT NULL,
    AGE INT,
    MEMBER_SINCE DATE DEFAULT (CURRENT_DATE),
    PRIMARY KEY(ID)
);

Now, let’s describe its structure.

DESCRIBE CUSTOMERS;

Output

The output clearly lists each column and its properties:

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL	auto_increment
NAME	varchar(100)	NO		NULL
EMAIL	varchar(100)	NO	UNI	NULL
AGE	int	YES		NULL
MEMBER_SINCE	date	YES		CURRENT_DATE

The SHOW COLUMNS Statement

SHOW COLUMNS is another command that produces the exact same output as DESCRIBE. It can sometimes be more readable in scripts.

SHOW COLUMNS FROM CUSTOMERS;

Part 2: Analyzing Query Execution with EXPLAIN

While DESCRIBE shows you what a table is, EXPLAIN shows you what MySQL does with a query. It reveals the query execution plan: how tables will be joined, what indexes will be used, and how many rows will be examined. It is an indispensable tool for performance tuning.

Important Distinction: Do not confuse DESCRIBE table_name with EXPLAIN SELECT .... The first describes schema, the second analyzes a query’s execution.

Syntax

EXPLAIN [FORMAT = format_name] SELECT_statement;
-- format_name can be TRADITIONAL, JSON, or TREE (MySQL 8.0+)

Example: Analyzing a Simple Query

Let’s analyze a simple SELECT query on our CUSTOMERS table.

EXPLAIN SELECT ID, NAME FROM CUSTOMERS WHERE EMAIL = 'test@example.com';

Because we have a UNIQUE key on the EMAIL column, MySQL can use it to find the row very efficiently. The type column in the output will show const or eq_ref, indicating a highly optimized lookup.

Understanding EXPLAIN Formats

The FORMAT option changes how the execution plan is displayed.

FORMAT = TRADITIONAL (Default)

The classic tabular format. Concise but requires knowledge to interpret columns like type, key, and Extra.

EXPLAIN FORMAT=TRADITIONAL SELECT * FROM CUSTOMERS;

FORMAT = JSON

Provides a much more detailed, structured output, including cost estimates for the query. Excellent for programmatic analysis.

EXPLAIN FORMAT=JSON SELECT * FROM CUSTOMERS;

FORMAT = TREE (MySQL 8.0+)

Displays the plan as a tree, which can be more intuitive for visualizing complex joins and subqueries.

EXPLAIN FORMAT=TREE SELECT * FROM CUSTOMERS;

Practical Application: Describing Tables in Code

Describing tables programmatically is useful for ORMs (Object-Relational Mappers), database administration tools, or dynamic code generators.

Node.js (with mysql2)
Python (with mysql-connector-python)



### Code
This example fetches the schema of the `CUSTOMERS` table and prints it in a formatted way.

```javascript
// Filename: describeTable.js
const mysql = require('mysql2/promise');

async function describeTable(tableName) {
  try {
    const pool = mysql.createPool({
      host: 'localhost', user: 'root', password: 'password', database: 'TUTORIALS'
    });

    const sql = `DESCRIBE ??`; // Use ?? for table/identifier escaping

    const [rows, fields] = await pool.query(sql, [tableName]);

    console.log(`Schema for table: '${tableName}'`);
    console.log('--------------------------------------------------');
    rows.forEach(col => {
        console.log(
            `Field: ${col.Field}, Type: ${col.Type}, Nullable: ${col.Null}, Key: ${col.Key || 'N/A'}`
        );
    });
    console.log('--------------------------------------------------');

    await pool.end();

  } catch (error) {
    console.error(`Failed to describe table:`, error);
  }
}

describeTable('CUSTOMERS');

Output

Schema for table: 'CUSTOMERS'
--------------------------------------------------
Field: ID, Type: int, Nullable: NO, Key: PRI
Field: NAME, Type: varchar(100), Nullable: NO, Key: N/A
Field: EMAIL, Type: varchar(100), Nullable: NO, Key: UNI
Field: AGE, Type: int, Nullable: YES, Key: N/A
Field: MEMBER_SINCE, Type: date, Nullable: YES, Key: N/A
--------------------------------------------------

Code

This example uses the mysql-connector-python to fetch and display the table schema.

describe_table.py

import mysql.connector
from mysql.connector import Error

def describe_table(table_name):
    """ Connects to MySQL and describes the given table. """
    try:
        with mysql.connector.connect(
            host='localhost', user='root', password='password', database='TUTORIALS'
        ) as connection:
            query = f"DESCRIBE {table_name}"

            # Using dictionary=True makes rows accessible by column name
            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(query)
                columns_info = cursor.fetchall()

                print(f"Schema for table: '{table_name}'")
                print('-' * 60)
                for col in columns_info:
                    print(
                        f"Field: {col['Field']:<15} Type: {col['Type']:<15} "
                        f"Nullable: {col['Null']:<5} Key: {col['Key'] or 'N/A'}"
                    )
                print('-' * 60)

    except Error as e:
        print(f"Error: {e}")

if __name__ == "__main__":
    describe_table('CUSTOMERS')

Output

Schema for table: 'CUSTOMERS'
------------------------------------------------------------
Field: ID              Type: int             Nullable: NO    Key: PRI
Field: NAME            Type: varchar(100)    Nullable: NO    Key: N/A
Field: EMAIL           Type: varchar(100)    Nullable: NO    Key: UNI
Field: AGE             Type: int             Nullable: YES   Key: N/A
Field: MEMBER_SINCE    Type: date            Nullable: YES   Key: N/A
------------------------------------------------------------