Complete Guide to MySQL Index Checking, Management, and Optimization

1. Introduction

MySQL is a relational database widely used in many web applications and data management systems. To improve data search speed, it features a mechanism called an “index.” However, if not managed properly, it can actually lead to performance degradation.

Why is Checking Indexes Important?

Indexes in a database are like the index in a book. Well-designed indexes improve the execution speed of search queries. However, the following issues can also occur.

• Indexes are not created appropriately
  → This can cause searches to slow down
• Unnecessary indexes exist
  → This leads to slower update and insert speeds
• It’s unclear which indexes are being used
  → Making it impossible to decide which unnecessary indexes to delete

What You Can Learn from This Article

• Basic mechanisms of MySQL indexes
• Methods to check current indexes (using SQL commands)
• Methods for managing and optimizing indexes
• Techniques for analyzing index usage

From here on, let’s systematically learn about MySQL indexes and apply this knowledge to improve database performance.

2. What is a MySQL Index?

Indexes are an important feature for improving database performance. Here, we explain the basic concepts of indexes, types, advantages, and disadvantages.

Basic Concepts of Indexes

A database index is a mechanism to quickly search for values in specific columns. For example, when searching for a specific record in a table with a large amount of data, without an index, it is necessary to scan all records (full table scan). Applying an index makes data searches efficient and significantly improves processing speed.

Types of MySQL Indexes

MySQL has several different types of indexes, each suited to specific uses.

1. PRIMARY KEY (Primary Key Index)

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

1. UNIQUE Index

• Constrains the specified column values from duplicating
• NULL values are allowed (multiple NULLs are OK)

1. INDEX (General Index)

• Used to speed up searches
• No problem even with duplicate data

1. FULLTEXT Index (For Text Search)

• Efficiently performs text searches
• Used in combination with the MATCH ... AGAINST syntax

1. SPATIAL Index (For Geographic Information)

• For spatial data (GIS data)

Advantages and Disadvantages of Indexes

Advantages

• Query search speed improves
• JOIN processing and WHERE clause performance improves
• Specific data retrieval becomes efficient

Disadvantages

• As indexes increase, data insertion, updates, and deletions slow down
• Consumes disk space
• If the index is not appropriate, performance may decrease instead

3. How to Check Indexes in MySQL

To manage indexes appropriately in MySQL, it is important to verify which indexes are configured on the current table. This section explains how to check indexes using the SHOW INDEX command, INFORMATION_SCHEMA.STATISTICS, mysqlshow command.

SHOW INDEX Command (Basic Check Method)

In MySQL, by using the SHOW INDEX command, you can obtain a list of indexes configured on a specific table. Using this command allows you to check detailed information such as the index name, applied columns, presence of unique constraint.

Basic Syntax

SHOW INDEX FROM table_name;

Execution Example

For example, to check the indexes set on the users table, execute the following SQL.

SHOW INDEX FROM users;

Example Result

Using INFORMATION_SCHEMA.STATISTICS (Obtaining Detailed Information)

By using MySQL’s system table INFORMATION_SCHEMA.STATISTICS, you can obtain the same information as the SHOW INDEX command more flexibly.

Checking 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';

Obtaining Indexes for the Entire Database

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

mysqlshow Command (Checking in CLI Environment)

It is also possible to obtain index information using MySQL’s command-line tool. It is particularly convenient when connecting to the MySQL server via SSH.

Command Execution Method

mysqlshow -u username -p password database_name table_name

Execution Example

mysqlshow -u root -p my_database users

Handling Cases with No Indexes

If no indexes are displayed even after executing the SHOW INDEX command or querying INFORMATION_SCHEMA.STATISTICS, it may indicate that appropriate indexes are not set on the table. In such cases, you can improve search performance by creating indexes as needed.

Creating a New Index

CREATE INDEX idx_column ON users (email);

Setting the Primary Key (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Deleting Unnecessary Indexes

ALTER TABLE users DROP INDEX idx_column;

4. How to Check Index Usage

Checking whether indexes are functioning correctly in MySQL is an important step in database performance optimization. In this section, we will explain how to check which indexes a query is using by utilizing the EXPLAIN command and Performance Schema.

Query Analysis Using EXPLAIN

EXPLAIN command is used to visualize how the specified SQL query is executed. By analyzing indexes used, search methods, execution plans, and so on, you can verify if the indexes are functioning properly.

Basic Syntax

EXPLAIN SELECT * FROM table_name WHERE condition;

Execution Example

For example, when searching the users table with a condition on the email column:

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

Example of Execution Results

Key Points

• If type = ALL, a full table scan is being performed, so you need to apply an index
• If an index name is displayed in key, that index is being used
• If the value in rows is too large, query optimization is necessary

Utilizing Performance Schema

By using MySQL’s performance_schema, you can analyze in detail which indexes are being used and to what extent during query execution.

Obtaining Query Execution Statistics

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

Checking 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';

Remedies When Indexes Are Not Being Used

1. Reviewing the Query

If an index is not being used, there may be an issue with how the query is written. For example, in cases like the following, the index may not be utilized.❌ Incorrect Writing (Index is invalidated because a function is used)

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

→ Due to LOWER(email), the index on the email column is ignored.✅ After Correction (No function used)

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

2. Recreating the Index

If the existing index is not functioning properly, recreating it may improve the situation.

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

3. Updating Statistics

If the table’s statistics are outdated, the index may not be used optimally. In that case, you can update the statistics with the following command.

ANALYZE TABLE users;

5. Index Management

MySQL indexes are important elements that improve data search performance, but if not managed properly, they can instead degrade database performance. This section provides a detailed explanation of creating and deleting indexes, as well as identifying and managing unnecessary indexes.

Creating Indexes

By creating appropriate indexes, you can 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

When adding an index to the email column of 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 the Primary Key (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Deleting Indexes

By deleting unnecessary indexes, you can reduce unnecessary overhead in the database.

Basic Syntax

ALTER TABLE table_name DROP INDEX index_name;

Example

For example, when deleting an index named idx_email:

ALTER TABLE users DROP INDEX idx_email;

Identifying and Deleting Unnecessary Indexes

Checking for Unused Indexes

SELECT * FROM sys.schema_unused_indexes;

Checking the Impact of Indexes

SHOW TABLE STATUS LIKE 'users';

Deleting Unnecessary Indexes

ALTER TABLE users DROP INDEX idx_unused;

After deletion, it is also recommended to run ANALYZE TABLE to update the statistics.

ANALYZE TABLE users;

6. Index Optimization (Performance Improvement)

By properly managing indexes, you can significantly improve MySQL’s query performance. However, rather than just creating indexes, by performing appropriate design, management, and monitoring, it is possible to maintain optimal performance.

Appropriate Index Design

MySQL indexes, when properly designed, can significantly improve search speed.

Cases Where Indexes Should Be Applied

Cases Where It’s Better Not to Apply Indexes

Utilizing Slow Query Logs

By using the Slow Query Log, you can identify queries that take a long time to execute and analyze which indexes are not being applied.

Enabling Slow Query Logs

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Record queries that take 2 seconds or more

Checking Slow Query Logs

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

Analyzing Slow Queries

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

Index Application Example

CREATE INDEX idx_last_login ON users(last_login);

Updating Statistics (ANALYZE & OPTIMIZE)

ANALYZE TABLE (Updating Statistics)

ANALYZE TABLE users;

OPTIMIZE TABLE (Defragmentation)

OPTIMIZE TABLE users;

7. Frequently Asked Questions about Indexes (FAQ)

MySQL indexes are an important mechanism for improving database performance, but if not managed properly, they can have the opposite effect. In this section, we have compiled frequently asked questions (FAQ) about MySQL indexes and their answers.

Does adding more indexes always improve search speed?

A. Not necessarily.Indexes are designed to improve the speed of search queries, but adding too many can actually degrade database performance.

• Increased load on writes (INSERT, UPDATE, DELETE)
• Whether an index is applied depends on the query
• Unnecessary indexes consume memory

Which columns should I set indexes on?

A. It is effective to apply indexes to columns like the following.

Are indexes created automatically?

A. Some indexes are created automatically, but there are cases where you need to add them manually.

Indexes Created Automatically

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

Indexes Created Manually

CREATE INDEX idx_email ON users(email);

How should I differentiate between index types (B-Tree, Hash, Fulltext)?

How to check the size of an index?

SHOW TABLE STATUS LIKE 'users';

How to check if an index is being used?

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

When should I delete an index?

Deleting Unnecessary Indexes

ALTER TABLE users DROP INDEX idx_unused;

8. Summary

In this article, we comprehensively explained MySQL indexes from the basics to checking methods, management, optimization, and frequently asked questions. Here, we will review the key points of each section and summarize best practices for optimizing MySQL index management.

Review of the Article’s Key Points

What are MySQL Indexes?

• Indexes are mechanisms to speed up data searches.
• There are types such as B-Tree, Hash, and FULLTEXT indexes.
• They are effective when applied to columns used in WHERE clauses, JOINs, and ORDER BY.

How to Check MySQL Indexes

• Check the list of indexes with the SHOW INDEX command.
• Detailed information can be obtained from INFORMATION_SCHEMA.STATISTICS.

How to Check Index Usage

• Use EXPLAIN to check which indexes the query is using.
• Usage frequency can be analyzed using the Performance Schema.

Index Management

• Set indexes on appropriate columns with CREATE INDEX.
• Delete unnecessary indexes with ALTER TABLE DROP INDEX.
• Check index sizes with SHOW TABLE STATUS and perform optimization.

Index Optimization (Performance Improvement)

• Columns to apply: Columns frequently used in WHERE, JOIN, ORDER BY.
• Use the slow query log to identify slow queries and optimize them.
• Update statistics with ANALYZE TABLE and OPTIMIZE TABLE.

Best Practices for MySQL Index Management

1. Before applying indexes, identify which queries are bottlenecks.
2. Select appropriate indexes

• Appropriately distinguish between single-column indexes and composite indexes.
• Use UNIQUE INDEX for columns that require unique constraints.

1. Regularly delete unnecessary indexes

• Identify unused indexes with SHOW INDEX or schema_unused_indexes.

1. Regularly update statistics

• Update statistics with ANALYZE TABLE
• Run OPTIMIZE TABLE to resolve fragmentation caused by deletions or updates.

Next Steps

✅ Practical Checklist✅Have you checked the current indexes with SHOW INDEX?✅Have you enabled the slow query log and identified slow queries?✅Have you analyzed the query execution plan with EXPLAIN?✅Have you deleted unnecessary indexes and created appropriate ones?✅Have you run ANALYZE TABLE to update statistics?

Final Summary

• Proper management of MySQL indexes can significantly improve search performance.
• Use the slow query log and EXPLAIN to analyze index effectiveness and perform optimization.
• Regularly update statistics and optimize tables to maintain database performance.

This concludes the series of articles on MySQL index management.

Use this knowledge to aim for faster and more efficient database operations. 💡🚀