How to Check Indexes on a Table in SQL

1. Overview

Indexes play a vital role in optimizing query performance in databases. By creating indexes on specific columns, we can significantly reduce the time it takes to retrieve data from large tables.

Whether we’re looking to improve the speed of a complex query, optimize database operations, or ensure efficient searching. Understanding how to check the existing indexes on a table is essential.

In this tutorial, we’ll explore how to check indexes on a table in various database systems: PostgreSQL, MySQL, and SQL Server. It’s important to note that each system offers its own methods and tools to view the indexes. We’ll use the full Baeldung University database for illustration.

2. Implementation in PostgreSQL

In PostgreSQL, checking indexes on a table is a straightforward process. We can use a combination of system catalog tables and specific commands to retrieve information about indexes. Additionally, PostgreSQL provides robust tools for monitoring and managing indexes, ensuring efficient query processing and data retrieval.

2.1. Using the \di Command in psql

In PostgreSQL, the psql command-line interface offers a simple way to list all indexes. Additionally, provides a summary of the available indexes in the current database.

For example, let’s check all the indexes in a database:

\di
+--------+--------------------+-------+-------+
| Schema | Name               | Type  | Owner |
|--------+--------------------+-------+-------|
| public | course_pkey        | index | user  |
| public | department_pkey    | index | user  |
| public | exam_pkey          | index | user  |
| public | faculty_pkey       | index | user  |
| public | prerequisite_pkey  | index | user  |
| public | program_pkey       | index | user  |
| public | registration_pkey  | index | user  |
| public | specification_pkey | index | user  |
| public | student_pkey       | index | user  |
| public | teaching_pkey      | index | user  |
+--------+--------------------+-------+-------+
SELECT 10
Time: 0.010s

In the output, we see a list of indexes that are defined in the public schema of the University database. Further, each row provides details about an index, including its Schema, Name, Type, and Owner.

2.2. Querying the pg_indexes System Catalog

For a more detailed view of the indexes on a specific table, we can query the pg_indexes system catalog. This method provides more granular control over the results and allows us to focus on a particular table’s indexes.

For example, let’s view all the indexes in the Student table:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'student';
+--------------+---------------------------------------------------------------------+
| indexname    | indexdef                                                            |
|--------------+---------------------------------------------------------------------|
| student_pkey | CREATE UNIQUE INDEX student_pkey ON public.student USING btree (id) |
+--------------+---------------------------------------------------------------------+
SELECT 1
Time: 0.048s

This query returns the index name and the definition of the index, including the columns involved. Additionally, the indexdef column provides the SQL statement that was used to create the index, giving us a deeper understanding of its structure.

2.3. Using the pg_catalog.pg_index System Table

Another method for checking indexes in PostgreSQL is by querying the pg_catalog.pg_index system table. This table holds information about all indexes in the database.

For example, let’s retrieve the indexes for the Course table:

SELECT i.indexrelid::regclass AS index_name, 
       array_to_string(array_agg(a.attname), ', ') AS index_columns
FROM pg_index i
JOIN pg_attribute a ON a.attnum = ANY(i.indkey)
WHERE i.indrelid = 'course'::regclass
GROUP BY i.indexrelid;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
| index_name  | index_columns                                                                                                                                                                                                              >
|-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
| course_pkey | oid, oid, attrelid, oid, oid, oid, inhrelid, indexrelid, oid, oid, oid, oid, oid, oid, oid, oid, loid, aggfnoid, starelid, oid, stxoid, oid, oid, oid, objoid, oid, oid, oid, oid, classid, oid, setdatabase, oid, oid, oid>
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
SELECT 1
Time: 0.030s

This query provides the index name and the columns that make up each index. In particular, giving a detailed breakdown of the index composition.

3. Implementation in MySQL

In MySQL, we can check indexes on a table using a few different methods. MySQL offers several commands that provide detailed information about the indexes present in the database.

3.1. Using the SHOW INDEX Command

The simplest way to check the indexes on a table in MySQL is by using the SHOW INDEX command. This command returns detailed information about all the indexes associated with a specific table, including the name, uniqueness, column involved, and index type.

For example, let’s view the indexes on the Student table:

SHOW INDEX FROM Student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Student |          0 | PRIMARY  |            1 | id          | A         |          22 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| Student |          0 | id       |            1 | id          | A         |          23 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.146 sec)

Let’s explain the important outputs of this command:

• Non_unique: indicates whether the index is unique (0 means it’s unique, 1 means it allows duplicates)
• Key_name: provides the name of the index
• Column_name: shows the columns that make up the index
• Index_type: indicates the type of index, which is typically BTREE in MySQL

This method gives us a quick overview of all indexes and their properties on the specified table.

3.2. Querying the information_schema.STATISTICS Table

For a more detailed view, we can query the information_schema.STATISTICS table. Furthermore, this table contains metadata about all the indexes in the MySQL database.

Additionally, this method allows us to filter by table name and retrieve specific index details.

For example, let’s check the indexes on the Department table:

SELECT INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX, NON_UNIQUE, INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'University' AND TABLE_NAME = 'Exam';
+------------+-------------+--------------+------------+------------+
| INDEX_NAME | COLUMN_NAME | SEQ_IN_INDEX | NON_UNIQUE | INDEX_TYPE |
+------------+-------------+--------------+------------+------------+
| id         | id          |            1 |          0 | BTREE      |
| PRIMARY    | id          |            1 |          0 | BTREE      |
+------------+-------------+--------------+------------+------------+
2 rows in set (0.001 sec)

The output gives a detailed information about the indexes:

• INDEX_NAME: the name of the index
• COLUMN_NAME: the columns involved in the index
• SEQ_IN_INDEX: the sequence number of the column in the index

Additionally, this method is especially useful when we need more control over the results or want to filter by specific index attributes.

4. Implementation in SQL Server

In SQL Server, there are several ways to check the indexes on a table. SQL Server provides both system stored procedures and system catalog views to retrieve detailed information about the indexes on a table. These methods give insight into the indexes, their types, and the columns involved.

4.1. Using sp_helpindex

The sp_helpindex system stored procedure is one of the simplest ways to check the indexes on a table in SQL Server. In particular, it provides a list of indexes along with their characteristics, such as whether they’re unique or clustered.

For example, let’s check the indexes on the Student table:

EXEC sp_helpindex 'Student';
Index_name    Index_description                                         Index_keys
------------- -------------------------------------------------------- ------------
PK_Student    clustered, unique, primary key located on PRIMARY         id
IX_Student    nonclustered located on PRIMARY                           last_name
2 rows in set (0.15 sec)

Let’s explain the important outputs of this command:

• Index_name: the name of the index
• Index_description: describes the type of index (clustered, nonclustered, unique, primary key)
• Index_keys: the column or columns that make up the index

This method provides a quick and easy way to view all indexes on a given table, including whether they’re unique or clustered.

4.2. Using sys.indexes

For more granular control and detailed information about the indexes, we can query the sys.indexes system catalog view. Additionally, this method retrieves detailed information about the indexes, such as the name, type, and whether the index is unique or clustered.

For example, let’s view all indexes on the Course table:

SELECT name AS index_name, type_desc, is_unique, is_primary_key
FROM sys.indexes
WHERE object_id = OBJECT_ID('Course');
index_name    type_desc            is_unique   is_primary_key
------------- ------------------- ----------- ----------------
PK_Course     CLUSTERED INDEX      1           1
IX_Course     NONCLUSTERED INDEX   0           0
2 rows in set (0.01 sec)

This approach gives us a flexible way to examine specific aspects of the indexes on a table.

4.3. Using sys.index_columns

To retrieve detailed information about the columns that make up the indexes, we can query the sys.index_columns catalog view. This is useful when we want to know the exact columns involved in each index, along with their position in the index.

For example, let’s get the index columns for the Student table:

SELECT i.name AS index_name, c.name AS column_name, ic.index_column_id
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('Student');
index_name    column_name    index_column_id
------------- -------------  ----------------
PK_Student    id             1
IX_Student    last_name      1

In the output, we see which columns are part of each index and their order, giving a more comprehensive understanding of the index structure.

5. Conclusion

In this article, we explored various methods to check the indexes of a table in PostgreSQL, MySQL, and SQL Server. Furthermore, by using the commands and queries outlined for each system, we can easily retrieve and analyze index details, ensuring databases run smoothly and effectively.