SQL Show Indexes

In relational databases, indexes are essential for optimizing query performance and speeding up data retrieval operations. By creating an efficient access path for specific columns, indexes help reduce the time it takes to fetch results from large datasets. Understanding how to manage, view, and optimize indexes is crucial for any database administrator.

In this article, we will explain how to use the SQL SHOW INDEXES statement, its role in database optimization, and how to list and view indexes effectively across various database systems.

SHOW INDEXES: How to View Indexes in SQL

The SHOW INDEXES statement is used to display detailed information about the indexes present in a specific table. This information includes the index name, columns involved, whether the index allows duplicates, and other important details. It helps database administrators analyze the existing indexes and determine if any indexes need optimization or removal.

The syntax for the SHOW INDEXES statement varies across different database management systems. Here are some common examples:

MySQL:

SHOW INDEXES FROM table_name;

Explanation:

This command displays all the indexes created for a particular table, providing details such as index name, column names, and more.

PostgreSQL:

SELECT * FROM pg_indexes WHERE tablename = 'table_name';

Explanation:

PostgreSQL uses the pg_indexes system catalog to store index details. This query lists all indexes associated with a specific table.

SQL Server:

SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('table_name');

Explanation:

In SQL Server, this query retrieves information about indexes for a given table by querying the sys.indexes catalog view.

Output of SHOW INDEXES

The SHOW INDEXES command provides valuable information about the indexes in a table. Below are the key columns in the output and their meanings:

Columns in the SHOW INDEXES Output:

1. table: The name of the table that has infomation about indexes being shown.
   
2. non_unique: It tells whether the index allows similar values. A value of 1 means repetition is allowed, but with a zero the index makes sure it don't repeat.
   
3. key_name: It tells us the name of the index. The name for primary key indexes is written as 'PRIMARY'.
   
4. seq_in_index: It shows the sequence number of a column in an index. For complex indexes (indexes made up of more than one column), this part shows where the column is in the index.
   
5. column_name: The title of the section that belongs to the index.
   
6. collation: Sets out the way that column is sorted inside the index. 'A' means ascending, 'B' means descending and NULL shows that the column is not sorted.
   
7. cardinality: An estimate of how many unique values are there in the index. Usually, a higher cardinality means better use of indexes for search queries.
   
8. sub_part: Shows how long the part of columns that are just partly indexed is. If NOTHING, the whole column is indexed.
   
9. index_type: Tells what kind of index is used, like BTREE, HASH,about or FULLTEXT.
   
10. visible: Shows if the list is seen by the query optimizer (say 'Yes' if it can be, and not say anything for no).

Example Of SQL Show Indexes

Let’s create a table and add indexes to it, and then use the SHOW INDEXES statement to view index details in MySQL. Additionally, we will demonstrate how different types of indexes such as primary, unique, and composite indexes impact the table's performance and indexing strategy.

Step 1: Create the orders Table

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2),
    PRIMARY KEY(order_id),
    INDEX idx_customer_id (customer_id), -- Regular index on customer_id
    UNIQUE INDEX unique_order_date (order_date), -- Unique index on order_date
    INDEX invisible_index (total_amount) INVISIBLE, -- Invisible index on total_amount
    INDEX composite_index (customer_id, order_date) COMMENT 'By customer and order date' -- Composite index on multiple columns
);

Explanation:

This creates the orders table with several indexes: a primary index on order_id, a regular index on customer_id, a unique index on order_date, an invisible index on total_amount, and a composite index on customer_id and order_date.

Step 2: Show Indexes on the orders Table

SHOW INDEXES FROM orders;

Output

Explanation:

The output shows all the indexes associated with the orders table, along with details like index name, column names, uniqueness, cardinality, and index type. It helps you understand how each index is structured and whether it's used for specific queries.

Key Features of Indexes:

1. Faster Data Retrieval: Indexes allow the database to access specific rows quickly without scanning the entire table.
   
2. Efficiency in Querying: By organizing data in a structured way, indexes make SELECT queries faster, which improves the overall performance of database operations.
   
3. Impact on Data Modifications: While indexes optimize SELECT queries, they can slow down INSERT, UPDATE, and DELETE operations, as these actions require the index to be updated as well.

Conclusion

The SHOW INDEXES statement is an essential tool for managing and optimizing indexes in SQL databases. By providing detailed information about the indexes in a table, it allows database administrators to monitor and adjust the indexing strategy based on query performance. Whether we're using MySQL, PostgreSQL, or SQL Server, understanding how to use SHOW INDEXES can help us fine-tune our indexing strategy, improve query performance, and ensure that our database remains efficient as it scales.