MySQL - Show Indexes

MySQL: How to Show and Analyze Table Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Instead of scanning an entire table to find the rows you’re looking for (a ‘full table scan’), the database can use an index to directly locate the data, similar to using the index in the back of a book. Properly indexed tables are crucial for database performance.

MySQL supports several types of indexes, including PRIMARY KEY, UNIQUE, INDEX (non-unique), and FULLTEXT. To manage performance, you need a way to see which indexes are defined on a table. This tutorial covers two primary methods.

Method 1: The SHOW INDEX Statement

The SHOW INDEX statement is a MySQL-specific command that provides detailed information about a table’s indexes.

Syntax

SHOW INDEX FROM table_name;
-- Alternative syntax:
SHOW INDEXES IN table_name FROM database_name;

Tip: In the MySQL command-line client, appending \G instead of ; to your query will format the output vertically, which is much more readable for wide result sets like this one.

Example

Let’s create a products table with several different indexes.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    category_id INT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    UNIQUE KEY `uk_sku` (sku),
    INDEX `idx_category_active` (category_id, is_active)
);

Now, let’s display the indexes on this table.

SHOW INDEX FROM products\G

Output

*************************** 1. row ***************************
        Table: products
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: products
   Non_unique: 0
     Key_name: uk_sku
 Seq_in_index: 1
  Column_name: sku
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: products
   Non_unique: 1
     Key_name: idx_category_active
 Seq_in_index: 1
  Column_name: category_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 4. row ***************************
        Table: products
   Non_unique: 1
     Key_name: idx_category_active
 Seq_in_index: 2
  Column_name: is_active
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

Understanding the SHOW INDEX Output

Here are the most important columns from the output:

• Key_name: The name of the index. For a PRIMARY KEY, the name is always PRIMARY.
• Non_unique: 0 if the index must have unique values (like a PRIMARY KEY or UNIQUE index), 1 otherwise.
• Seq_in_index: The column’s sequence number within the index, starting from 1. This is important for composite indexes (indexes on multiple columns). Our idx_category_active has two rows, one for each column in the index.
• Column_name: The name of the indexed column.
• Cardinality: An estimate of the number of unique values in the index. A higher cardinality relative to the number of rows indicates a more selective and effective index. This value is updated periodically.
• Index_type: The index method used, typically BTREE for most storage engines.

Method 2 (Modern): Querying INFORMATION_SCHEMA

A more standard and flexible way to retrieve metadata about database objects is to query the INFORMATION_SCHEMA. The STATISTICS table contains information about table indexes.

Syntax

SELECT
    INDEX_NAME,
    COLUMN_NAME,
    NON_UNIQUE,
    SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

Example

Let’s get the index information for our products table, assuming it’s in a database named store.

SELECT
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'store' AND TABLE_NAME = 'products'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

Output

INDEX_NAME	COLUMN_NAME	SEQ_IN_INDEX	NON_UNIQUE
PRIMARY	id	1	0
idx_category_active	category_id	1	1
idx_category_active	is_active	2	1
uk_sku	sku	1	0

SHOW INDEX vs. INFORMATION_SCHEMA

• SHOW INDEX: A quick, simple command for interactive use in the MySQL client. Its output format is MySQL-specific.
• INFORMATION_SCHEMA: The SQL-standard way to query metadata. It is more powerful and flexible because you can use standard SELECT syntax, including WHERE clauses, JOINs (e.g., to get column data types from INFORMATION_SCHEMA.COLUMNS), and ORDER BY. This method is highly recommended for programmatic access.

Displaying Index Information in Application Code

For scripts or applications that need to inspect database schemas, querying the INFORMATION_SCHEMA is the best practice. Here’s how to do it in various languages.

Python (mysql-connector)
Node.js (async/await)
Java (JDBC)
PHP (PDO)



This Python script retrieves and prints index information in a structured way.

```python
# main.py
import mysql.connector

config = { 'user': 'root', 'password': 'password' }

db_name = 'store'
tbl_name = 'products'

sql = """
SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE, SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
ORDER BY INDEX_NAME, SEQ_IN_INDEX
"""

with mysql.connector.connect(**config) as cnx:
    with cnx.cursor(dictionary=True) as cursor:
        cursor.execute(sql, (db_name, tbl_name))
        print(f"Indexes on table '{db_name}.{tbl_name}':")
        for index in cursor:
            print(f" - Index: {index['INDEX_NAME']}, Column: {index['COLUMN_NAME']}, "
                  f"Seq: {index['SEQ_IN_INDEX']}, Unique: {not index['NON_UNIQUE']}")

This mysql2/promise example fetches and displays index metadata.

main.js

import mysql from 'mysql2/promise';

async function showIndexes() {
  let connection;
  const dbName = 'store';
  const tableName = 'products';
  const sql = `
    SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE, SEQ_IN_INDEX
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
    ORDER BY INDEX_NAME, SEQ_IN_INDEX;
  `;

  try {
    connection = await mysql.createConnection({ /* connection config */ });
    const [indexes] = await connection.execute(sql, [dbName, tableName]);

    console.log(`Indexes on table '${dbName}.${tableName}':`);
    indexes.forEach(idx => {
      console.log(
        ` - Index: ${idx.INDEX_NAME}, Column: ${idx.COLUMN_NAME}, ` +
        `Seq: ${idx.SEQ_IN_INDEX}, Unique: ${!idx.NON_UNIQUE}`
      );
    });
  } finally {
    if (connection) await connection.end();
  }
}
showIndexes();

Java’s JDBC provides a dedicated DatabaseMetaData API for this, which is an even higher-level abstraction.

Main.java

import java.sql.*;

public class ShowIndexExample {
    // ... connection details ...
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            DatabaseMetaData metaData = conn.getMetaData();
            // Params: catalog, schema, table, unique, approximate
            try (ResultSet rs = metaData.getIndexInfo("store", null, "products", false, false)) {
                System.out.println("Indexes on table 'store.products':");
                while (rs.next()) {
                    System.out.printf(" - Index: %s, Column: %s, Seq: %d, Unique: %b\n",
                            rs.getString("INDEX_NAME"),
                            rs.getString("COLUMN_NAME"),
                            rs.getShort("ORDINAL_POSITION"),
                            !rs.getBoolean("NON_UNIQUE"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This PHP script uses a PDO prepared statement to query INFORMATION_SCHEMA.

<?php
require 'config.php'; // Assumes a file with PDO connection $pdo

$db_name = 'store';
$table_name = 'products';

$sql = "
    SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE, SEQ_IN_INDEX
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = :db_name AND TABLE_NAME = :table_name
    ORDER BY INDEX_NAME, SEQ_IN_INDEX
";

$stmt = $pdo->prepare($sql);
$stmt->execute(['db_name' => $db_name, 'table_name' => $table_name]);

$indexes = $stmt->fetchAll();

echo "Indexes on table '$db_name.$table_name':\n";
foreach ($indexes as $index) {
    $is_unique = !$index['NON_UNIQUE'] ? 'true' : 'false';
    echo sprintf(" - Index: %s, Column: %s, Seq: %d, Unique: %s\n",
        $index['INDEX_NAME'],
        $index['COLUMN_NAME'],
        $index['SEQ_IN_INDEX'],
        $is_unique
    );
}
?>