Optimizing search speed is a critical issue in database operations. One solution is the use of “indexes.” Indexes are essential for speeding up data retrieval in a database. This article explains how to inspect indexes in MySQL, covering everything from basics to advanced techniques.
What You’ll Learn in This Article
The basic mechanisms of indexes and their types
How to check indexes in MySQL (specific examples using SHOW INDEX and EXPLAIN commands)
Proper management and maintenance of indexes
Common questions about indexes and their solutions
When used properly, indexes can dramatically improve database performance. However, improper configuration or management can actually degrade overall system performance. By reading this article, you’ll master indexes from fundamentals to advanced usage and apply that knowledge to improve database operations.
2. Index Basics
Indexes are mechanisms that improve database search efficiency and play a crucial role in data management. This section explains the basic workings of indexes, their types, and their benefits and challenges.
What is an Index?
An index is like a “catalog” set on a specific column in a database. It speeds up data retrieval. It’s similar to a book’s table of contents, used to locate needed information efficiently. For example, without an index, the database must scan every row sequentially (full table scan). With an index, you can quickly reach the target data by traversing the index structure.
Types of Indexes
Primary Key Index (PRIMARY KEY) An index automatically created on the primary key. It ensures each row is uniquely identifiable and there is only one per table.
Unique Index (UNIQUE) Ensures the values in the specified column are unique. Used when duplicate values are not allowed.
Full‑Text Index (FULLTEXT) An index that speeds up text searching. Primarily used for full‑text search scenarios.
Composite Index You can create an index that combines multiple columns.
Example: By setting a composite index on both name and age, search performance improves for conditions based on both.
Benefits and Challenges of Indexes
Benefits
Improved Search Speed Data queries and condition‑based extraction become faster.
Better Query Efficiency Processing of WHERE clauses, JOINs, ORDER BY, and similar operations is significantly improved.
Challenges
Performance Degradation on Data Updates Because indexes must be updated, INSERT, UPDATE, and DELETE operations can become slower.
Storage Consumption Indexes require additional storage, and large indexes can consume significant disk space.
3. How to Check Indexes in MySQL
MySQL provides several ways to check the status of indexes. In this section, we’ll explain three representative methods—“SHOW INDEX command,” “INFORMATION_SCHEMA.STATISTICS table,” and “EXPLAIN command”—with concrete examples.
How to Check Using the SHOW INDEX Command
SHOW INDEX command is the basic command for viewing details of indexes defined on a table.
Basic Syntax
SHOW INDEX FROM table_name;
Example
For example, to check the indexes of the users table, run the following.
SHOW INDEX FROM users;
Sample Result
Table
Non_unique
Key_name
Seq_in_index
Column_name
Collation
Cardinality
Index_type
Comment
users
0
PRIMARY
1
id
A
1000
BTREE
users
1
idx_name
1
name
A
500
BTREE
Explanation of Output Columns
Key_name: Index name.
Non_unique: Uniqueness (0 = unique, 1 = not unique).
Column_name: Name of the column the index is defined on.
Cardinality: Estimated number of unique values in the index.
Index_type: Type of the index (usually BTREE).
How to Check Using 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 view index information for the users table in my_database:
SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = 'my_database'
AND table_name = 'users';
Sample Result (Excerpt)
TABLE_SCHEMA
TABLE_NAME
INDEX_NAME
COLUMN_NAME
INDEX_TYPE
my_database
users
PRIMARY
id
BTREE
my_database
users
idx_name
name
BTREE
This method is useful for efficiently retrieving index information across a specific database or multiple tables.
How to Check Using the EXPLAIN Command
EXPLAIN command is a tool for viewing the execution plan of an SQL query 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 Result
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
users
ref
idx_name
idx_name
102
const
1
Using index
Explanation of Output Columns
key: Name of the index actually used.
possible_keys: Indexes that could be used.
rows: Estimated number of rows scanned.
Extra: Index usage details and additional information.
Conclusion
In MySQL, you can use SHOW INDEX, INFORMATION_SCHEMA.STATISTICS, and the EXPLAIN command to check index status and how they are used in queries. Each method has its own characteristics, so choose the appropriate one based on your needs.
4. Index Management
Properly managing indexes in MySQL is essential for efficient database operation. This section provides a detailed explanation of how to create, drop, and optimize indexes.
Creating Indexes
Basic Syntax
Indexes are created 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 Composite Indexes
It is also possible to create an index that combines multiple columns.
CREATE INDEX idx_name_email ON users(name, email);
Using a composite index can streamline queries with multiple search conditions.
Dropping Indexes
Basic Syntax
Unneeded indexes are removed 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 reduces unnecessary storage usage and can improve performance during data updates.
Index Optimization and Maintenance
Identifying Low-Usage Indexes
Low-usage indexes can become a burden on the database. Use the following query to check index usage.
SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = 'database_name'
AND table_name = 'table_name';
Removing Redundant Indexes
If multiple indexes are set on the same column, removing them can improve efficiency.
Example of Using Tools
Use Percona Toolkit to automatically detect redundant indexes.
When indexes are fragmented, performance degrades. Rebuilding the indexes can improve performance.
ALTER TABLE table_name ENGINE=InnoDB;
Summary
Managing indexes involves not only creating and dropping them but also optimizing and performing regular maintenance. Proper management helps maintain database performance and enables efficient operation.
5. FAQ (Frequently Asked Questions)
Questions about MySQL indexes are a common concern for many people. This section compiles frequently asked questions about indexes and their answers. Reading this will deepen your understanding of how indexes work and how to manage them.
Why an index might not be used?
Even when an index is defined, the query may not use it. Below are the main reasons and how to address them.
Main Reasons
Query syntax errors When the query uses a pattern that prevents index usage (e.g., a leading wildcard like LIKE '%keyword%').
Data type mismatch When the data type of the value specified in the query differs from the column’s data type used when the index was created.
Small tables When the database decides that a full table scan is more efficient.
Solutions
Use the EXPLAIN command Check the execution plan to see whether the index is being used.
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
Optimize the query Rewrite the conditions so the index can be utilized.
What to watch out for when creating composite indexes?
Composite indexes are effective for speeding up searches with multiple conditions, but you need to be aware of certain considerations when creating them.
Considerations
Column order matters Place the columns most frequently used in search conditions first. For example, in WHERE name = 'Alice' AND age > 25, put name first.
Range conditions should come later If a condition involves a range (e.g., age > 30), place it later in the index.
Avoid excessive composite indexes Including columns that are rarely used can degrade performance.
When can indexes degrade performance?
Indexes are beneficial in most cases, but they can sometimes hurt performance.
Main Causes
Over-indexing Creating more indexes than needed adds extra overhead during data inserts and updates.
Fragmentation Fragmented indexes slow down search performance.
Duplicate indexes Having multiple indexes on the same column is redundant and wastes resources.
Remedies
Drop unused indexes.
Rebuild indexes regularly.
How to verify index effectiveness?
The following methods help verify whether an index is functioning effectively.
Use the EXPLAIN command Check the execution plan and ensure the key column shows the index name.
Leverage the query performance schema Use MySQL’s Performance Schema to analyze index usage in detail.
Use performance monitoring tools Utilize tools like Percona Toolkit to diagnose index performance.
What is the optimal number of indexes?
The ideal number of indexes varies by use case and table characteristics; consider the following points.
Key Points
Design based on frequently used queries Create only the indexes required by specific queries.
Minimize indexes on high‑write tables Limit indexes to the essential ones to reduce the overhead of data updates.
6. Summary
MySQL indexes are a crucial factor that dramatically improves database search efficiency. This article systematically explains everything from the basics of indexes to advanced topics, as well as concrete management methods and answers to common questions.
Reviewing the article’s key points
Fundamentals and types of indexes
Indexes function as the database’s “index,” improving search efficiency.
There are various types such as primary key indexes, unique indexes, full-text indexes, and composite indexes, each suited to different purposes.
How to check indexes in MySQL
You can easily check the status of indexes and their usage in queries using the SHOW INDEX and EXPLAIN commands.
By leveraging the INFORMATION_SCHEMA.STATISTICS table, you can obtain more detailed information.
Index management and optimization
By properly creating and dropping necessary indexes, you can improve search efficiency while reducing update overhead.
Removing redundant indexes and addressing fragmentation are also important.
Frequently asked questions about indexes
The FAQ section answers practical questions such as why an index might not be used and considerations for composite indexes.
Next steps
Check the current index status of your database Use SHOW INDEX or EXPLAIN to investigate the indexes set on your tables.
Execute performance optimization Identify low-use or redundant indexes and delete them as needed.
Practice proper index design Create and adjust indexes based on frequently used queries.
Leverage what you’ve learned Use the knowledge from this article to improve database operation efficiency.
Conclusion
Proper index management not only boosts database performance but also enhances overall system efficiency. However, over-indexing or poor design can actually degrade performance. Use this article as a reference to further hone your index management skills and aim for stable database operations.
