How to Show Indexes on Table or Database in MySQL

Indexes make it easy to quickly to fetch specific rows from database tables. It is available in every database management system. Without indexes, a database server will need to sequentially search each row, starting from the first, for specific column value. Often database developers and administrators need to find the indexes on table or database in MySQL. In this article, we will learn how to show indexes on table in MySQL.

How to Show Indexes on Table or Database in MySQL

There are several ways to show indexes on table or database in MySQL.

SHOW INDEXES FROM table_name;
OR

SHOW INDEXES FROM db_name.table_name;
OR

SHOW INDEXES FROM table_name in db_name;
OR

SHOW KEYS FROM table_name in db_name;

For example, if you want to view indexes on table sales in database ecommerce, you can do so using the following SQL queries.

SHOW INDEXES FROM sales;
OR

SHOW INDEXES FROM ecommerce.sales;
OR

SHOW INDEXES FROM sales in ecommerce;
OR

SHOW KEYS FROM sales in ecommerce;

If you want to view all indexes for all tables in database, you can do so using the following SQL query.

mysql> SELECT
         DISTINCT TABLE_NAME,
         INDEX_NAME
      FROM
          INFORMATION_SCHEMA.STATISTICS
      WHERE
          TABLE_SCHEMA = 'database_name';

If you want to view the indexes for all tables in database ecommerce, you can do it with the following SQL query.

mysql> SELECT
         DISTINCT TABLE_NAME,
         INDEX_NAME
      FROM
          INFORMATION_SCHEMA.STATISTICS
      WHERE
          TABLE_SCHEMA = 'ecommerce';

In this article, we have learnt how to show indexes on table or database in MySQL.

Also read:

How to Set Global SQL Mode in MySQL
How to Check if Column is Empty or Not in MySQL
How to Create Please Wait Loading Animation in jQuery
How to Get Current URL in jQuery
How to Get Class List for DOM Element