How to Check Indexes in MySQL: SHOW INDEX, EXPLAIN & Optimization Guide

1. Introduction

In database operations, optimizing search speed is a critical challenge. One of the key solutions is leveraging “indexes.” Indexes are essential features for accelerating data retrieval in databases. In this article, we will explain how to check indexes in MySQL, from the basics to advanced usage.

What You Will Learn in This Article

• The basic structure and types of indexes
• How to check indexes in MySQL (including practical examples using SHOW INDEX and EXPLAIN)
• Proper index management and maintenance techniques
• Common questions about indexes and their solutions

When used correctly, indexes can dramatically improve database performance. However, improper configuration or management can negatively impact overall system performance. Through this article, you will learn everything from basic concepts to advanced techniques and apply them to improve your database operations.

2. Basic Knowledge of Indexes

An index is a mechanism designed to improve search efficiency in a database, playing a vital role in data management. In this section, we will explain the basic structure, types, advantages, and challenges of indexes.

What Is an Index?

An index is similar to a “lookup table” created for specific columns in a database. It improves data retrieval speed. It functions like a book’s table of contents, helping you quickly locate the necessary information.

Without an index, a database must scan all target data sequentially (a full table scan). However, when an index is present, the database can quickly access the desired data by traversing the index structure.

Types of Indexes

1. Primary Key Index (PRIMARY KEY)
   This index is automatically created for the primary key. It ensures that each row is uniquely identifiable and only one exists per table.
2. Unique Index (UNIQUE)
   Guarantees that values in the specified column are unique. It is used when duplicate values must not be allowed.
3. Full-Text Index (FULLTEXT)
   An index designed to speed up text searches. It is primarily used for full-text search operations.
4. Composite Index
   It is possible to create an index that combines multiple columns.
   Example: Setting a composite index based on both name and age improves performance for search conditions involving both columns.

Advantages and Challenges of Indexes

Advantages

1. Improved Search Speed
   Data retrieval and filtering based on specific conditions become significantly faster.
2. Enhanced Query Efficiency
   Processing speed for WHERE clauses, JOIN operations, and ORDER BY clauses improves dramatically.

Challenges

1. Performance Degradation During Data Updates
   Since indexes must also be updated, INSERT, UPDATE, and DELETE operations may become slower.
2. Storage Consumption
   Indexes require additional storage space, and large indexes can consume significant disk capacity.

3. How to Check Indexes in MySQL

MySQL provides several ways to check the status of indexes. In this section, we will walk through three common methods with practical examples: the SHOW INDEX command, the INFORMATION_SCHEMA.STATISTICS table, and the EXPLAIN command.

How to Check with the SHOW INDEX Command

The SHOW INDEX command is a fundamental command for checking detailed information about indexes defined on a table.

Basic Syntax

SHOW INDEX FROM table_name;

Example

For example, to check indexes on the users table, run the following:

SHOW INDEX FROM users;

Sample Output

Field Descriptions

• Key_name: The name of the index.
• Non_unique: Uniqueness (0 means unique, 1 means not unique).
• Column_name: The column name the index is defined on.
• Cardinality: The estimated number of unique values in the index.
• Index_type: The index type (typically BTREE).

How to Check with INFORMATION_SCHEMA.STATISTICS

INFORMATION_SCHEMA.STATISTICS is a system table that stores index information within the database.

Basic Syntax

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

Example

To check index information for the users table in my_database:

SELECT * FROM INFORMATION_SCHEMA.STATISTICS 
WHERE table_schema = 'my_database' 
AND table_name = 'users';

Sample Output (Excerpt)

This method is useful when you want to efficiently retrieve index information for a specific database or across multiple tables.

How to Check with the EXPLAIN Command

The EXPLAIN command is a tool for inspecting an SQL query’s execution plan and analyzing how indexes are used.

Basic Syntax

EXPLAIN query;

Example

Check the execution plan for the following query:

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

Sample Output

Field Descriptions

• key: The name of the index actually used.
• possible_keys: Indexes that could be used.
• rows: The estimated number of rows to scan.
• Extra: Additional details about index usage and execution.

Summary

In MySQL, you can use SHOW INDEX, INFORMATION_SCHEMA.STATISTICS, and EXPLAIN to check index status and analyze how indexes are used in queries. Since each method has different strengths, choose the approach that best fits your needs.

4. Index Management

Proper index management in MySQL is essential for efficient database operations. In this section, we will explain in detail how to create, drop, and optimize indexes.

Creating an Index

Basic Syntax

Create an index using the CREATE INDEX statement.

CREATE INDEX index_name ON table_name(column_name);

Example

For example, to create an index on the email column of the users table:

CREATE INDEX idx_email ON users(email);

Creating a Composite Index

You can also create an index that combines multiple columns.

CREATE INDEX idx_name_email ON users(name, email);

Using a composite index can improve performance for queries that use multiple search conditions.

Dropping an Index

Basic Syntax

Drop an index that is no longer needed using the DROP INDEX statement.

DROP INDEX index_name ON table_name;

Example

For example, to drop the idx_email index from the users table:

DROP INDEX idx_email ON users;

Dropping indexes can reduce unnecessary storage usage and improve performance during data updates.

Index Optimization and Maintenance

Identify Low-Usage Indexes

Indexes that are rarely used can become a burden on the database. Use the following query to review index details.

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

Remove Redundant Indexes

If multiple indexes are defined on the same column, you can improve efficiency by removing redundant ones.

Example: Using a Tool

Use Percona Toolkit to automatically detect redundant indexes.

pt-duplicate-key-checker --host=localhost --user=root --password=yourpassword

Fix Index Fragmentation

If indexes become fragmented, performance can degrade. In that case, rebuilding indexes may improve performance.

ALTER TABLE table_name ENGINE=InnoDB;

Summary

Index management is not only about creating and dropping indexes—optimization and regular maintenance are also important. Proper management helps maintain database performance and enables efficient operations.

5. FAQ (Frequently Asked Questions)

Many people have questions about MySQL indexes. In this section, we summarize common questions and their answers. By reading this, you will gain a deeper understanding of how indexes work and how to manage them effectively.

Why Is an Index Not Being Used?

Even when an index is defined, it may not be used in a query. Below are the main reasons and possible solutions.

Main Reasons

1. Incorrect Query Structure
   If the query uses syntax that prevents index usage (e.g., LIKE '%keyword%' with a leading wildcard).
2. Data Type Mismatch
   If the data type of the value specified in the query differs from the column’s data type defined in the index.
3. Small Table Size
   If the database optimizer determines that a full table scan is more efficient.

Solutions

• Use the EXPLAIN Command
  Check the execution plan to verify whether the index is being used.

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

• Optimize the Query
  Modify the conditions so that the index can be utilized effectively.

What Should You Be Careful About When Creating a Composite Index?

A composite index is effective for speeding up multi-condition searches, but there are important considerations when creating one.

Key Points

1. Column Order Matters
   Place frequently used search columns first. Example: In WHERE name = 'Alice' AND age > 25, put name first in the index.
2. Range Conditions Should Come Later
   If including a range condition (e.g., age > 30), place it after equality conditions.
3. Avoid Excessive Composite Indexes
   Including rarely used columns may reduce performance.

When Can Indexes Reduce Performance?

Although indexes are beneficial in many cases, they can sometimes reduce performance depending on the situation.

Main Causes

1. Too Many Indexes
   Creating more indexes than necessary increases overhead during INSERT and UPDATE operations.
2. Fragmentation
   If an index becomes fragmented, search performance may decrease.
3. Duplicate Indexes
   Multiple indexes on the same column are redundant and waste resources.

Countermeasures

• Remove unused indexes.
• Rebuild indexes periodically.

How Can You Verify Index Effectiveness?

To verify whether indexes are functioning effectively, use the following methods:

1. Use the EXPLAIN Command
   Check the execution plan and confirm that the index name appears in the key column.
2. Leverage the Performance Schema
   Use MySQL’s Performance Schema to analyze index usage in detail.
3. Use Performance Monitoring Tools
   Utilize tools such as Percona Toolkit to diagnose index performance.

What Is the Optimal Number of Indexes?

The optimal number of indexes depends on usage patterns and table characteristics. Consider the following points:

Guidelines

• Design Based on Frequently Used Queries
  Create indexes only for queries that are frequently executed.
• Minimize Indexes on Frequently Updated Tables
  Keep indexes to a minimum to reduce update overhead.

6. Conclusion

MySQL indexes are a critical component for significantly improving database search efficiency. In this article, we systematically covered everything from basic concepts to advanced management techniques, along with practical FAQs.

Key Takeaways

1. Basic Concepts and Types of Indexes

• Indexes function as a database “lookup structure” and improve search efficiency.
• There are various types, including Primary Key, Unique, Full-Text, and Composite indexes.

1. How to Check Indexes in MySQL

• You can easily verify index status and usage using SHOW INDEX and EXPLAIN.
• Using the INFORMATION_SCHEMA.STATISTICS table provides more detailed insights.

1. Index Management and Optimization

• Properly creating and removing indexes improves search efficiency while reducing update overhead.
• Removing redundant indexes and resolving fragmentation are also essential.

1. Frequently Asked Questions

• The FAQ section addressed practical concerns, such as why indexes are not used and best practices for composite indexes.

Next Steps

1. Review Your Current Index Configuration
   Use SHOW INDEX and EXPLAIN to analyze the indexes defined on your tables.
2. Optimize Performance
   Identify low-usage or redundant indexes and remove them if necessary.
3. Implement Proper Index Design
   Create and adjust indexes based on frequently used queries.
4. Apply What You’ve Learned
   Use the knowledge from this article to improve your database operations.

Final Thoughts

Proper index management not only enhances database performance but also improves overall system efficiency. However, excessive indexing or poor design can degrade performance. Use this article as a reference to refine your index management skills and achieve stable, high-performance database operations.