How to Check and Optimize MySQL Indexes: Complete Guide with SHOW INDEX, EXPLAIN, and Performance Tuning

1. Introduction

MySQL is a relational database widely used in many web applications and data management systems. To improve data retrieval speed, a mechanism called an “index” exists. However, if not properly managed, indexes can actually reduce performance.

Why Is Checking Indexes Important?

An index in a database is similar to the index section of a book. Properly designed indexes improve the execution speed of search queries. However, the following issues may occur:

• Indexes are not properly created
  → Can cause slow searches
• Unnecessary indexes exist
  → Leads to slower updates and inserts
• Uncertainty about which indexes are being used
  → Makes it difficult to decide whether unused indexes should be removed

What You Will Learn in This Article

• The basic mechanism of MySQL indexes
• How to check current indexes (using SQL commands)
• How to manage and optimize indexes
• Techniques to analyze index usage

Now, let’s systematically learn about MySQL indexes and use this knowledge to improve database performance.

2. What Is a MySQL Index?

An index is an essential feature for improving database performance. In this section, we explain the basic concept, types, advantages, and disadvantages of indexes.

Basic Concept of Indexes

A database index is a mechanism that allows fast searching of values in specific columns. For example, when searching for a specific record in a table containing a large amount of data, without an index, the database must scan all records (full table scan). Applying an index makes data retrieval efficient and significantly improves processing speed.

Types of MySQL Indexes

MySQL supports several types of indexes, each suitable for specific use cases.

1. PRIMARY KEY (Primary Key Index)

• Can be set only once per table
• Guarantees uniqueness of the table
• Functions as a clustered index

1. UNIQUE Index

• Ensures values in the specified column do not duplicate
• NULL values are allowed (multiple NULLs are permitted)

1. INDEX (General Index)

• Used to speed up searches
• Duplicate data is allowed

1. FULLTEXT Index (for text search)

• Optimizes text searching
• Used in combination with the MATCH ... AGAINST syntax

1. SPATIAL Index (for geographic data)

• Designed for spatial (GIS) data

Advantages and Disadvantages of Indexes

Advantages

• Improves query search speed
• Enhances performance of JOIN operations and WHERE clauses
• Makes retrieval of specific data more efficient

Disadvantages

• More indexes slow down INSERT, UPDATE, and DELETE operations
• Consumes disk space
• Poorly designed indexes can degrade performance

3. How to Check MySQL Indexes

To manage MySQL indexes properly, it is important to check which indexes are currently defined on a table. In this section, we explain how to check indexes using the SHOW INDEX command, INFORMATION_SCHEMA.STATISTICS, and the mysqlshow command.

SHOW INDEX Command (Basic Method)

In MySQL, you can use the SHOW INDEX command to retrieve a list of indexes defined on a specific table. This command allows you to check details such as the index name, columns covered by the index, and whether a unique constraint exists.

Basic Syntax

SHOW INDEX FROM table_name;

Example

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

SHOW INDEX FROM users;

Example Output

Using INFORMATION_SCHEMA.STATISTICS (Get Detailed Info)

By using MySQL’s system table INFORMATION_SCHEMA.STATISTICS, you can retrieve the same information as SHOW INDEX with more flexibility.

Check Indexes for a Specific Table

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'users';

Get Indexes Across the Entire Database

SELECT TABLE_NAME, COLUMN_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name';

mysqlshow Command (Check from CLI)

You can also retrieve index information using MySQL command-line tools. This is especially useful when working via SSH on a MySQL server.

How to Run the Command

mysqlshow -u username -p password database_name table_name

Example

mysqlshow -u root -p my_database users

What to Do If No Indexes Exist

If no indexes are displayed after running SHOW INDEX or querying INFORMATION_SCHEMA.STATISTICS, the table may not have appropriate indexes. In that case, you can improve search performance by creating indexes as needed.

Create a New Index

CREATE INDEX idx_column ON users (email);

Set a Primary Key (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Drop Unnecessary Indexes

ALTER TABLE users DROP INDEX idx_column;

4. How to Check Index Usage

Checking whether indexes are functioning correctly is a critical step in MySQL performance optimization. In this section, we explain how to determine which indexes a query is using by leveraging the EXPLAIN command and Performance Schema.

Query Analysis with EXPLAIN

The EXPLAIN command is used to visualize how a given SQL query will be executed. It helps you analyze the indexes used, access methods, and execution plan, which makes it useful for verifying whether indexes are working as intended.

Basic Syntax

EXPLAIN SELECT * FROM table_name WHERE condition;

Example

For example, to search the users table using the email column as a condition:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Example Output

Key Points

• If type = ALL, the query is doing a full table scan, so you likely need an index.
• If an index name appears in key, that index is being used.
• If the rows value is too large, query optimization may be necessary.

Using Performance Schema

With MySQL’s performance_schema, you can analyze in detail which indexes are being used and how frequently during query execution.

Get Query Execution Statistics

SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%SELECT%';

Check Index Usage for a Specific Table

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database_name' AND OBJECT_NAME = 'users';

What to Do If an Index Is Not Used

1. Review the Query

If an index is not being used, the query structure may be the problem. For example, the following pattern may prevent index usage.

❌ Incorrect Example (Function disables index usage)

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

→ Because of LOWER(email), the index on email may be ignored.

✅ Corrected Example (Avoid using a function)

SELECT * FROM users WHERE email = 'test@example.com';

2. Recreate the Index

If an existing index is not functioning properly, dropping and recreating it can sometimes improve performance.

ALTER TABLE users DROP INDEX idx_email;
CREATE INDEX idx_email ON users(email);

3. Update Statistics

If table statistics become outdated, MySQL may not use indexes optimally. You can refresh statistics with the following command:

ANALYZE TABLE users;

5. Index Management

MySQL indexes are essential for improving data retrieval performance. However, if not properly managed, they can degrade overall database performance. In this section, we explain in detail how to create, delete, and identify unnecessary indexes.

Creating Indexes

By creating appropriate indexes, you can significantly speed up data searches. In MySQL, you can add indexes using CREATE INDEX or ALTER TABLE.

Basic Syntax

CREATE INDEX index_name ON table_name(column_name);

Example

To add an index to the email column in the users table:

CREATE INDEX idx_email ON users(email);

Multi-Column Index (Composite Index)

CREATE INDEX idx_name_email ON users(last_name, first_name, email);

Unique Index

CREATE UNIQUE INDEX idx_unique_email ON users(email);

Setting a Primary Key (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Dropping Indexes

Removing unnecessary indexes helps reduce database overhead.

Basic Syntax

ALTER TABLE table_name DROP INDEX index_name;

Example

For example, to delete an index named idx_email:

ALTER TABLE users DROP INDEX idx_email;

Identifying and Removing Unnecessary Indexes

Check for Unused Indexes

SELECT * FROM sys.schema_unused_indexes;

Check Table Status (Index Impact)

SHOW TABLE STATUS LIKE 'users';

Remove Unnecessary Indexes

ALTER TABLE users DROP INDEX idx_unused;

After deletion, it is recommended to update statistics using ANALYZE TABLE.

ANALYZE TABLE users;

6. Index Optimization (Performance Improvement)

Proper index management can significantly improve MySQL query performance. However, simply creating indexes is not enough — proper design, management, and monitoring are required to maintain optimal performance.

Proper Index Design

Well-designed indexes can dramatically improve search speed in MySQL.

Cases Where Indexes Should Be Applied

Cases Where Indexes Should Not Be Applied

Using the Slow Query Log

The Slow Query Log allows you to identify long-running queries and analyze which indexes are not being applied.

Enable the Slow Query Log

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Log queries taking longer than 2 seconds

Check the Slow Query Log

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

Analyze a Slow Query

EXPLAIN SELECT * FROM users WHERE last_login > '2024-01-01';

Example of Applying an Index

CREATE INDEX idx_last_login ON users(last_login);

Updating Statistics (ANALYZE & OPTIMIZE)

ANALYZE TABLE (Update Statistics)

ANALYZE TABLE users;

OPTIMIZE TABLE (Defragmentation)

OPTIMIZE TABLE users;

7. FAQ About Indexes (Frequently Asked Questions)

MySQL indexes are an essential mechanism for improving database performance. However, if not properly managed, they can have the opposite effect. In this section, we summarize frequently asked questions (FAQ) and answers related to MySQL indexes.

Will search speed improve as I add more indexes?

A. Not necessarily.

Indexes are designed to improve query performance, but adding too many indexes can actually degrade database performance.

• Increases write overhead (INSERT, UPDATE, DELETE)
• Whether an index is used depends on the query
• Unnecessary indexes consume memory

Which columns should have indexes?

A. It is effective to apply indexes to the following types of columns:

Are indexes created automatically?

A. Some indexes are created automatically, but others must be added manually.

Automatically Created Indexes

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY, -- PRIMARY KEY index
  email VARCHAR(255) UNIQUE -- Index automatically created by UNIQUE constraint
);

Manually Created Indexes

CREATE INDEX idx_email ON users(email);

How should I choose between B-Tree, Hash, and FULLTEXT indexes?

How can I check the size of indexes?

SHOW TABLE STATUS LIKE 'users';

How can I check whether an index is being used?

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

When should I remove an index?

Remove an Unnecessary Index

ALTER TABLE users DROP INDEX idx_unused;

8. Summary

In this article, we comprehensively covered MySQL indexes—from fundamentals to verification methods, management, optimization, and frequently asked questions. Here, we review the key points from each section and summarize best practices for optimizing MySQL index management.

Key Takeaways

What Is a MySQL Index?

• An index is a mechanism that speeds up data retrieval.
• There are several types, including B-Tree, Hash, and FULLTEXT indexes.
• Most effective when applied to columns used in WHERE clauses, JOINs, and ORDER BY.

How to Check MySQL Indexes

• Use the SHOW INDEX command to view the index list.
• Use INFORMATION_SCHEMA.STATISTICS to retrieve detailed information.

How to Check Index Usage

• Use EXPLAIN to see which indexes a query uses.
• Use Performance Schema to analyze index usage frequency.

Index Management

• Use CREATE INDEX to apply indexes to appropriate columns.
• Use ALTER TABLE DROP INDEX to remove unnecessary indexes.
• Check index size with SHOW TABLE STATUS and optimize as needed.

Index Optimization (Performance Improvement)

• Apply indexes to frequently used WHERE, JOIN, and ORDER BY columns.
• Use the Slow Query Log to identify and optimize slow queries.
• Update statistics with ANALYZE TABLE and OPTIMIZE TABLE.

MySQL Index Management Best Practices

1. Identify query bottlenecks before applying indexes.
2. Select appropriate indexes.

• Properly use single-column and composite indexes.
• Use UNIQUE INDEX where uniqueness constraints are required.

1. Regularly remove unnecessary indexes.

• Identify unused indexes using SHOW INDEX and schema_unused_indexes.

1. Update statistics regularly.

• Update statistics using ANALYZE TABLE.
• Run OPTIMIZE TABLE to resolve fragmentation caused by deletes and updates.

Next Steps

✅ Practical Checklist
✅ Have you checked current indexes using SHOW INDEX?
✅ Have you enabled the Slow Query Log and identified slow queries?
✅ Have you analyzed the query execution plan using EXPLAIN?
✅ Have you removed unnecessary indexes and created appropriate ones?
✅ Have you updated statistics using ANALYZE TABLE?

Final Summary

• Properly managed MySQL indexes significantly improve search performance.
• Use the Slow Query Log and EXPLAIN to analyze index effectiveness and optimize accordingly.
• Maintain database performance by regularly updating statistics and optimizing tables.

This concludes the complete guide to MySQL index management.

Use this knowledge to build faster and more efficient database systems. 💡🚀