The World of MySQL & MariaDB

MySQL Index Guide: Boost Database Performance with Proper Indexing

Performance Optimization
目次

1. What Is a MySQL Index: The Key to Improving Database Performance

Using indexes effectively in a MySQL database can significantly improve query performance. An index is a data structure generated for specific columns in a database, designed to speed up searches and filtering operations. For example, when extracting specific information from a large dataset, an index allows MySQL to skip scanning the entire table and instead search only the designated column within the index.

Roles and Types of Indexes

MySQL indexes come in the following types:
  • PRIMARY (Primary Key): A unique key allowed only once per table, serving as the main identifier of the table.
  • UNIQUE Index: An index that enforces uniqueness, preventing duplicate values from being inserted into the specified column.
  • Regular Index: An index without uniqueness constraints, used to improve search efficiency on specific columns.
In this way, indexes enhance the efficiency of searches and data operations on tables, making them indispensable especially for large datasets. However, having too many indexes can slow down INSERT and UPDATE operations, so it’s important to manage indexes only as needed.

2. Basic Methods to Check Indexes in MySQL

In MySQL, you can check existing indexes using the SHOW INDEX command. This is a simple SQL command that displays index information within a specified table. Below are the basic steps.

Basic Syntax and Output of SHOW INDEX

SHOW INDEX FROM table_name;

Explanation of Output

When you run this command, the following information is displayed:
  • Table: The name of the table where the index exists
  • Non_unique: Indicates whether the index is unique (0) or allows duplicates (1)
  • Key_name: The name of the index
  • Column_name: The column name to which the index applies
  • Cardinality: An estimate of the number of unique values registered in the index, used as a metric for search efficiency.
By using this information, you can visually understand the index status within a table and how indexes are applied to each column. You can also narrow down the display target using the WHERE clause.

3. Checking Indexes with the INFORMATION_SCHEMA.STATISTICS Table

Besides the SHOW INDEX statement, MySQL also allows you to check indexes by querying the INFORMATION_SCHEMA.STATISTICS table. This method is useful for listing indexes across the entire database and retrieving more detailed information.

Basic Query for INFORMATION_SCHEMA.STATISTICS

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

Details of Query Results

  • TABLE_SCHEMA: The name of the database where the index belongs
  • TABLE_NAME: The name of the table where the index exists
  • COLUMN_NAME: The column name to which the index applies
  • INDEX_NAME: The name of the index
By using this method, you can check index information across multiple tables or within a specific database in one view. This approach is particularly helpful when managing indexes at the database-wide level.

4. How to Add and Remove Indexes and Their Effects

How to Add an Index

Indexes can be added later as needed. Use the following command to create an index on a specified column:
CREATE INDEX index_name ON table_name(column_name);
For example, if you want to add an index to the email column in the users table, run the following:
CREATE INDEX idx_email ON users(email);

How to Remove an Index

Unnecessary indexes can be removed to optimize the performance of INSERT and UPDATE operations. To delete an index, use the DROP INDEX command:
DROP INDEX index_name ON table_name;
Examples of unnecessary indexes include those created on columns that are never used in WHERE clauses. Removing such indexes can improve the speed of data insertion and updates.

5. Verifying Index Performance with the EXPLAIN Statement

The MySQL EXPLAIN statement is useful for checking query execution plans and investigating which indexes are being applied. This helps evaluate the effectiveness of indexes and optimize them when necessary.

Basic Usage of EXPLAIN

EXPLAIN SELECT * FROM table_name WHERE column_name = 'condition';
Using this command, you can determine whether an index is being used or if a full table scan is being performed. The results include the following items:
  • type: The type of query (ALL means full table scan, INDEX means an index is used)
  • possible_keys: A list of indexes that could be used for the query
  • key: The actual index being used
  • rows: The estimated number of rows to be scanned
With this information, you can analyze the effectiveness of indexes and determine whether further optimization is needed to improve search performance.

6. Conclusion

Proper index management is essential for optimizing the performance of MySQL databases. Especially when handling large tables, adding indexes to columns used in WHERE and JOIN clauses can dramatically improve query efficiency. However, too many indexes can slow down insert and update operations, so maintaining the right balance is crucial. By understanding how to add, check, delete, and evaluate indexes with performance tools, you can easily optimize your database and enhance the overall efficiency of your system.