MySQL Show Indexes

Introduction

Indexes are crucial for optimizing database query performance. However, as your database grows, keeping track of which indexes exist, their types, and their configurations becomes increasingly challenging. MySQL provides several commands that allow you to inspect and analyze the indexes in your database. The primary command for this purpose is SHOW INDEXES, which displays detailed information about all indexes in a table.

In this tutorial, you'll learn how to use the SHOW INDEXES command and related statements to view index information, understand the output format, and diagnose potential index-related issues in your MySQL databases.

The SHOW INDEXES Command

Basic Syntax

The basic syntax for displaying indexes is:

sql

SHOW INDEXES FROM table_name [FROM database_name];
-- OR
SHOW INDEX FROM table_name [FROM database_name];
-- OR
SHOW KEYS FROM table_name [FROM database_name];

All three forms (SHOW INDEXES, SHOW INDEX, and SHOW KEYS) are aliases and produce identical results.

Example Usage

Let's create a simple table with various indexes to demonstrate:

sql

CREATE DATABASE IF NOT EXISTS store;
USE store;

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10, 2),
    stock_quantity INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    UNIQUE INDEX idx_name (product_name),
    INDEX idx_price_stock (price, stock_quantity)
);

Now, let's examine the indexes on this table:

sql

SHOW INDEXES FROM products;

The output will look similar to this:

+-----------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name       | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| products  |          0 | PRIMARY        |            1 | product_id    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| products  |          0 | idx_name       |            1 | product_name  | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| products  |          1 | idx_category   |            1 | category      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| products  |          1 | idx_price_stock|            1 | price         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| products  |          1 | idx_price_stock|            2 | stock_quantity| A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Understanding the Output

Let's break down the columns in the SHOW INDEXES output:

1. Table: The name of the table containing the index.

2. Non_unique: Indicates if the index allows duplicate values:

   • 0 = No duplicates allowed (unique index)
   • 1 = Duplicates allowed (non-unique index)

3. Key_name: The name of the index. Note that the primary key is always named PRIMARY.

4. Seq_in_index: The column's position within the index (starting from 1). This is important for multi-column indexes.

5. Column_name: The name of the column that is indexed.

6. Collation: The sort order for the index:

   • 'A' = Ascending
   • 'D' = Descending
   • NULL = Not sorted

7. Cardinality: An estimate of unique values in the index. Higher cardinality generally means the index is more useful for queries.

8. Sub_part: If only a prefix of the column is indexed, this shows the number of characters or bytes indexed. NULL means the entire column is indexed.

9. Packed: Indicates how the key is packed. NULL means it's not packed.

10. Null: Contains YES if the column may contain NULL values; blank if NULL values are not allowed.

11. Index_type: The indexing method used (most commonly BTREE in MySQL).

12. Comment: Any comments provided for the index.

13. Index_comment: Comments specified during index creation using the COMMENT keyword.

14. Visible: Indicates whether the index is visible to the query optimizer (YES or NO).

15. Expression: For functional indexes, contains the indexed expression.

Filtering and Sorting the Output

You can add conditions to filter or sort the index information:

sql

-- Filter indexes by name
SHOW INDEXES FROM products WHERE Key_name = 'idx_category';

-- Sort indexes by column name
SHOW INDEXES FROM products ORDER BY Column_name;

Alternative Ways to View Indexes

There are several alternatives to SHOW INDEXES that provide similar information:

Using INFORMATION_SCHEMA

The INFORMATION_SCHEMA.STATISTICS table contains detailed index information:

sql

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = 'store' AND table_name = 'products';

This method offers more flexibility for filtering and analyzing index data.

Using DESCRIBE or SHOW CREATE TABLE

For a simpler view of indexes:

sql

-- Shows basic table structure including keys
DESCRIBE products;

-- Shows complete table definition including all indexes
SHOW CREATE TABLE products;

Example output for SHOW CREATE TABLE:

CREATE TABLE `products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(100) NOT NULL,
  `category` varchar(50) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `stock_quantity` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`product_id`),
  UNIQUE KEY `idx_name` (`product_name`),
  KEY `idx_category` (`category`),
  KEY `idx_price_stock` (`price`,`stock_quantity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Practical Examples

Example 1: Analyzing Index Coverage for a Query

Suppose you have a query that filters products by category and price range:

sql

-- First, let's check which indexes might be useful for this query
SHOW INDEXES FROM products WHERE Column_name IN ('category', 'price');

-- Now, let's run the query with EXPLAIN to see which indexes are used
EXPLAIN SELECT * FROM products 
WHERE category = 'Electronics' AND price BETWEEN 100 AND 500;

The output from EXPLAIN will show you which indexes the query optimizer chooses to use.

Example 2: Finding Tables Without Indexes

To identify tables that might benefit from indexing:

sql

SELECT t.table_schema, t.table_name, t.table_rows
FROM information_schema.tables t
LEFT JOIN information_schema.statistics s
  ON t.table_schema = s.table_schema 
  AND t.table_name = s.table_name
WHERE t.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
  AND s.index_name IS NULL
  AND t.table_rows > 100;

This query finds tables with more than 100 rows but no indexes.

Example 3: Identifying Duplicate Indexes

Duplicate or overlapping indexes waste space and slow down writes:

sql

SELECT t.table_schema, t.table_name, 
       GROUP_CONCAT(DISTINCT index_name ORDER BY index_name) AS indexes,
       GROUP_CONCAT(DISTINCT column_name ORDER BY seq_in_index) AS columns
FROM information_schema.statistics s
JOIN information_schema.tables t
  ON s.table_schema = t.table_schema 
  AND s.table_name = t.table_name
WHERE t.table_schema = 'store'
GROUP BY t.table_schema, t.table_name, column_name
HAVING COUNT(DISTINCT index_name) > 1;

Common Scenarios Where SHOW INDEXES Is Useful

1. Database Optimization: Identifying missing or inefficient indexes
2. Troubleshooting Slow Queries: Seeing if the right indexes are available
3. Database Documentation: Creating an inventory of all indexes
4. Migration Planning: Understanding the index structure before moving data
5. Storage Analysis: Indexes take space, so knowing what exists helps manage storage

Best Practices for Index Management

1. Regular Index Review: Periodically review your indexes using SHOW INDEXES
2. Check Cardinality: Low cardinality indexes (e.g., on boolean fields) are often inefficient
3. Monitor Duplicate Indexes: Avoid having multiple indexes covering the same columns
4. Check Usage: Consider dropping unused indexes after confirming they aren't needed
5. Document Index Purpose: Use index comments to document why each index exists

sql

-- Adding a comment to an index during creation
CREATE INDEX idx_last_modified ON orders (last_modified_date) COMMENT 'For monthly report queries';

-- Adding a comment to an existing index
ALTER TABLE orders ALTER INDEX idx_last_modified COMMENT 'For monthly report queries';

Summary

The SHOW INDEXES command is an essential tool for MySQL database administrators and developers. It provides detailed information about all indexes in a table, helping you understand their structure, purpose, and potential effectiveness.

In this tutorial, you've learned:

• The basic syntax of SHOW INDEXES and its variations
• How to interpret each column in the output
• Alternative methods for viewing index information
• Practical examples for analyzing and managing indexes
• Best practices for index management

By regularly reviewing your indexes, you can ensure your database performs optimally and avoids common pitfalls like missing or redundant indexes.

Additional Resources

To deepen your understanding of MySQL indexes, consider exploring these topics:

1. MySQL index types (B-tree, hash, fulltext, spatial)
2. Index statistics and the MySQL query optimizer
3. Using ANALYZE TABLE to update index statistics
4. Invisible indexes for testing index removal
5. Performance impact of indexes on INSERT, UPDATE, and DELETE operations

Exercises

1. Create a new table with at least three indexes of different types (primary key, unique, regular index).
2. Use SHOW INDEXES to display and analyze the indexes you created.
3. Write a query using INFORMATION_SCHEMA.STATISTICS to find all unique indexes in a database.
4. Run SHOW INDEXES and EXPLAIN on the same table, and analyze how the index information relates to query execution plans.
5. Find a table in your database with a multi-column index and analyze its structure using SHOW INDEXES.