Questions
4 of 19
1What is an index in MySQL and why is it used?
2How do you create an index on a table column?
3What are the different types of indexes available in MySQL?
4How can you view all indexes defined on a table?
5What is the difference between a primary key and a unique index?
6How do indexes improve query performance? Explain with an example.
7What is a composite index, and how does column order affect its usage?
8How do clustered and non-clustered indexes differ in MySQL’s InnoDB engine?
9What is a full-text index and when would you use it?
10Explain the concept of index selectivity and how it impacts performance.
11How does MySQL use indexes internally when executing a JOIN query?
12What is a covering index, and how can it reduce disk I/O?
13What are the trade-offs of adding too many indexes to a table?
14How can partial indexes or prefix indexes be used for large text columns?
15Describe how InnoDB’s clustered index structure affects row storage and lookups.
16What are invisible indexes in MySQL 8.0, and how can they be used for testing?
17How does MySQL optimize queries differently when using B-Tree vs. HASH indexes?
18What happens to indexes during bulk inserts, and how can you optimize performance for such operations?
19How do you identify and remove unused or redundant indexes without affecting application performance?
mysqlmysql
Basics
Databases
Tables
Data Types
Queries
Views
Indexes
Clauses
Regex
Functions and Operators
Keys
Joins
Triggers
04 / 19

How can you view all indexes defined on a table?

How to View All Indexes Defined on a Table in MySQL

MySQL provides several ways to list all indexes on a specific table. These commands help you inspect index names, columns, uniqueness, index types, and more.

1. Using SHOW INDEX
  1. 1

    The most common method to view indexes.

  2. 2

    Displays index name, columns, uniqueness, cardinality, and index type.

Syntax
2. Using INFORMATION_SCHEMA.STATISTICS
  1. 1

    Provides detailed metadata about indexes.

  2. 2

    Useful when you need to query/filter index information programmatically.

Query Example
3. Using DESCRIBE (Partial Info Only)
  1. 1

    Shows PRIMARY KEY and UNIQUE constraints.

  2. 2

    Does NOT list all secondary indexes.

Command

The recommended and most complete method is using SHOW INDEX or querying INFORMATION_SCHEMA.STATISTICS for more detailed control.