SQL SHOW INDEXES

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

Introduction

In this chapter, we will focus on how to display indexes in a SQL database. Knowing how to view indexes on a table can help you understand the existing indexing strategy and make decisions about adding or removing indexes. This chapter will cover the syntax and provide examples for showing indexes in different database systems.

What is SHOW INDEXES?

The SHOW INDEXES command (or its equivalent) is used to display information about the indexes on a table. This information typically includes the index name, the columns involved in the index, and other relevant details.

Syntax for SHOW INDEXES

MySQL

SHOW INDEXES FROM table_name;

SQL Server

EXEC sp_helpindex 'table_name';

PostgreSQL

SELECT * FROM pg_indexes WHERE tablename = 'table_name';

Step-by-Step Example

Sample Table and Indexes

First, let's create a sample table named employees and add some indexes to it.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2)
);

Inserting Sample Data

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ravi', 'Verma', 'ravi.verma@example.com', 3, 50000);

Creating Indexes

To create some indexes on the last_name and email columns:

CREATE INDEX idx_last_name
ON employees (last_name);

CREATE UNIQUE INDEX idx_email
ON employees (email);

Showing Indexes

MySQL Example

To show indexes on the employees table:

SHOW INDEXES FROM employees;

Output

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type
employees	0	PRIMARY	1	id	A	5	NULL	NULL		BTREE
employees	1	idx_last_name	1	last_name	A	5	NULL	NULL	YES	BTREE
employees	0	idx_email	1	email	A	5	NULL	NULL	YES	BTREE

SQL Server Example

To show indexes on the employees table:

EXEC sp_helpindex 'employees';

Output

index_name	index_description	index_keys
PK__employees__3213E83F7F60ED59	clustered, unique, primary key located on PRIMARY	id
idx_last_name	nonclustered located on PRIMARY	last_name
idx_email	nonclustered, unique, located on PRIMARY	email

PostgreSQL Example

To show indexes on the employees table:

SELECT * FROM pg_indexes WHERE tablename = 'employees';

Output

schemaname	tablename	indexname	indexdef
public	employees	employees_pkey	CREATE UNIQUE INDEX employees_pkey ON public.employees USING btree (id)
public	employees	idx_last_name	CREATE INDEX idx_last_name ON public.employees USING btree (last_name)
public	employees	idx_email	CREATE UNIQUE INDEX idx_email ON public.employees USING btree (email)

Conclusion

Knowing how to display indexes on a table is essential for understanding and managing your database schema. This chapter covered the basic syntax for showing indexes in MySQL, SQL Server, and PostgreSQL, provided examples to illustrate its use, and demonstrated how to interpret the output. Understanding how to use SHOW INDEXES effectively will help you optimize the performance of your database queries.

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Spring Boot + RabbitMQ Course - The Practical Guide

SQL SHOW INDEXES

Introduction

What is SHOW INDEXES?

Syntax for SHOW INDEXES

MySQL

SQL Server

PostgreSQL

Step-by-Step Example

Sample Table and Indexes

Inserting Sample Data

Creating Indexes

Showing Indexes

MySQL Example

Output

SQL Server Example

Output

PostgreSQL Example

Output

Conclusion

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Build REST APIs with Spring Boot 4, Spring Security 7, and JWT

[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25

ChatGPT + Generative AI + Prompt Engineering for Beginners

Spring 7 and Spring Boot 4 for Beginners (Includes 8 Projects)

Building Real-Time REST APIs with Spring Boot - Blog App

Building Microservices with Spring Boot and Spring Cloud

Java Full-Stack Developer Course with Spring Boot and React JS

Build 5 Spring Boot Projects with Java: Line-by-Line Coding

Testing Spring Boot Application with JUnit and Mockito

Spring Boot Thymeleaf Real-Time Web Application - Blog App

Master Spring Data JPA with Hibernate

Spring Boot + Apache Kafka Course - The Practical Guide

Java Testing: Mastering JUnit 5 Framework

Reactive Programming in Java: Spring WebFlux and Testing

Spring Boot + RabbitMQ Course - The Practical Guide

Functional Programming in Java (Includes Java Collections)

ChatGPT for Java Developers: Boost Your Productivity with AI

Comments

Post a Comment

Spring Boot 3 Paid Course Published for Free on my Java Guides YouTube Channel

Spring Boot 3 Paid Course Published for Free
on my Java Guides YouTube Channel