SQL - Show Indexes

SQL - How to Show Indexes

Why Show Indexes?

Database indexes are crucial for query performance. They work like the index in a book, allowing the database to find data quickly without scanning the entire table. As a developer or database administrator, you’ll often need to list the indexes on a table to:

• Verify Schema: Ensure that the correct indexes have been created as intended.
• Troubleshoot Performance: Analyze if a slow query is missing a necessary index or using an inefficient one.
• Optimize Queries: Understand the available indexes before writing or refactoring complex queries.
• Manage Database Objects: Identify unused or redundant indexes that can be dropped to save space and reduce write overhead.

The Standard Method: INFORMATION_SCHEMA

The most portable and standard way to retrieve index information is by querying the INFORMATION_SCHEMA.STATISTICS view. This schema is defined by the ANSI SQL standard and is available in most modern relational databases, including PostgreSQL, MySQL, and SQL Server.

Syntax

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

Example

First, let’s create a sample table with a primary key (which automatically gets an index) and a multi-column index.

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    DepartmentID INT NOT NULL
);

-- Create a non-unique index on LastName and FirstName
CREATE INDEX IX_Employees_Name ON Employees(LastName, FirstName);

Now, let’s query the INFORMATION_SCHEMA to see our indexes (example uses a generic schema name public, adjust as needed).

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

Output (Conceptual)

The output will show each column within each index.

INDEX_NAME	COLUMN_NAME	SEQ_IN_INDEX	NON_UNIQUE
PRIMARY	ID	1	0
IX_Employees_Name	LastName	1	1
IX_Employees_Name	FirstName	2	1

SEQ_IN_INDEX indicates the column’s position in a multi-column index. NON_UNIQUE is 0 (false) for unique indexes and 1 (true) for non-unique ones.

Database-Specific Methods

While INFORMATION_SCHEMA is standard, most databases also provide their own native commands or system views, which can sometimes offer more detailed, vendor-specific information.

Showing Indexes in PostgreSQL

In the psql command-line tool, you can use the \d meta-command for a user-friendly view.

\d your_table_name

-- Example:
\d employees

This command displays table columns, indexes, constraints, and other details. To query programmatically, you can use the pg_indexes system view.

SELECT * FROM pg_indexes WHERE tablename = 'employees';

Showing Indexes in MySQL

MySQL provides the convenient SHOW INDEX statement.

SHOW INDEX FROM TableName;

-- Example:
SHOW INDEX FROM Employees;

Showing Indexes in SQL Server

A traditional method is to use the sp_helpindex system stored procedure.

EXEC sp_helpindex 'TableName';

-- Example:
EXEC sp_helpindex 'dbo.Employees';

For more detailed and modern programmatic access, querying the system catalog views like sys.indexes is preferred.

SELECT
    i.name AS index_name,
    c.name AS column_name,
    ic.key_ordinal
FROM
    sys.indexes i
INNER JOIN
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE
    i.object_id = OBJECT_ID('dbo.Employees');

Practical Application: Connecting Indexes to Query Performance

Listing indexes is the first step. The next step is to see if the database is actually using them. This is done with an EXPLAIN or EXPLAIN ANALYZE command (syntax varies slightly by database).

-- PostgreSQL / MySQL Example
EXPLAIN ANALYZE SELECT * FROM Employees WHERE LastName = 'Smith';

When you run this, the query plan will tell you if it used an ‘Index Scan’ (good, it used your index) or a ‘Sequential Scan’ / ‘Table Scan’ (bad, it read the whole table). By listing your indexes and analyzing the query plan, you can effectively diagnose and solve performance bottlenecks.