MySQL Index: Usage, Management & Troubleshooting

Introduction

MySQL is a relational database management system that organizes data in tables and stores values in rows and columns. An index is a data structure that makes the information more searchable. Without an index, MySQL reads through the entire table to find the required row, which significantly impacts efficiency.

This guide will explain what a MySQL index is, how to use and manage indexes, and how to troubleshoot common indexing issues.

Prerequisites

• A working MySQL installation (this guide uses MySQL on Ubuntu 22.04).
• A MySQL user account with root privileges.
• Access to a command line/terminal.

Indexing in MySQL Databases

Indexes help database administrators quickly find rows with specific column values. Databases manage data of any size, including large databases with millions of rows, which makes locating specific rows in larger databases challenging.

When a table is indexed, MySQL quickly determines the position to seek within the data file without scanning all the data. Thus, indexing prevents MySQL from reading every row. Without an index, MySQL must start from the first row and read through the entire table to find the relevant rows. Users cannot see the indexes; they are used internally to speed up searches and queries.

Types of Indexes in MySQL

MySQL indexes optimize query performance, ensuring faster data retrieval and efficient database management. Different index types serve various purposes, catering to specific query needs. The following sections present different index types.

Primary Key as Index

A primary key index is a type of unique index in which the primary key column must always contain unique, non-null values. It uniquely identifies each row in the table, facilitating fast data retrieval and ensuring data integrity by preventing duplicate and null entries.

A primary key is set on a single column or multiple columns (though using multiple columns is generally not recommended). Once set, the values in the primary key cannot be changed.

Multiple-Column Index

MySQL can create indexes on multiple columns (up to 16 columns). It uses multiple-column indexes for queries that test all the columns in the index or just the first column, the first two, the first three columns, etc. If you specify the columns correctly in the index definition, a single composite index speeds up several query types on the same table.

Unique Index

Unique indexes in MySQL ensure all values in the indexed column or columns are distinct, preventing duplicate entries. They enforce data integrity by maintaining uniqueness, essential for certain database operations and constraints.

Unique indexes allow null values, but each non-null value must remain unique. These indexes are defined on one or multiple columns and are commonly used for columns that require unique data, such as usernames or email addresses.

Full-Text Index

A full-text index is an index applied to fields that utilize full-text search capabilities. It allows for complex queries on large text data and supports searches for words and phrases.

Full-text indexes are handy for searching large text fields where traditional indexes are inefficient. They are created on CHAR, VARCHAR, or TEXT columns.

Descending Index

A descending index in MySQL allows you to create an index where the specified columns are organized in descending order. This index type improves query performance when sorting the results in descending order.

It is beneficial for applications where recent data, like logs or time-series data, needs to be accessed more frequently. By default, MySQL creates indexes in ascending order, but specifying descending order optimizes certain query patterns.

Spatial Index

Spatial indexes in MySQL are designed to optimize queries on spatial data types, such as POINT, LINESTRING, and POLYGON, which are used to store geometric data. They enable efficient querying and analysis of spatial relationships, such as proximity, intersection, and containment.

Spatial indexes use R-tree structures, which are well-suited for indexing multidimensional data. These indexes are only created on spatial data columns within tables that use the InnoDB or MyISAM storage engines.

Hash vs. B-tree Indexes

Hash indexes excel at fast exact matches, while B-tree indexes offer versatility, supporting range queries and sorting. Hash indexes are used for equality comparisons but lack support for range queries and order-dependent operations. Thus, they are suitable for the MEMORY storage engine in MySQL.

On the other hand, B-tree indexes, with their balanced tree structure, handle a variety of queries, including exact matches and range queries. They store data in a sorted order, enhancing search performance, and they are supported by most MySQL storage engines.

CREATE INDEX Statement

The CREATE INDEX statement in MySQL creates an index on one or more columns of a table. Depending on the storage engine and index type chosen, it can be either a B-tree index or a hash index.

A CREATE INDEX statement in MySQL uses the following syntax:

CREATE INDEX [index_name] ON [table_name] ([column_name],[other_column_name]);

• index_name. The name of the index.
• table_name. The name of the table with columns being indexed.
• column_name, other_column_name. The names of the columns to be indexed.

The following is a MySQL CREATE INDEX statement example:

CREATE INDEX index1 ON table1 (column1,column2);

Create Index for New MySQL Table

Follow the steps below to create an index and a table at the same time:

1. Open a terminal window and log into the MySQL shell:

mysql -u root -p

2. Create a new database by entering the following command:

CREATE DATABASE mytest;

3. Switch to the new database with:

USE mytest;

4. Next, create a new table with four columns, and index them:

CREATE TABLE example (
col1 INT PRIMARY KEY,
col2 INT NOT NULL,
col3 INT NOT NULL,
col4 VARCHAR(20),
INDEX (col2, col3)
);

The command creates a table named example, populates it with four columns, and adds indexes for column 2 and column 3. The CREATE INDEX statement is not separate because it's integrated into the CREATE TABLE command using the INDEX clause.

5. Display a list of the indexes from the table:

SHOW INDEXES FROM example;

The output shows an index for PRIMARY, col2, and col3. Even though PRIMARY was not specified, MySQL automatically created an index for the primary key. The output shows:

• The primary key index (PRIMARY) on col1 in the first row. It indicates that col1 is the first column in the primary key index, and the index type is BTREE.
• A non-unique index (col2) on col2 in the second row. It shows that col2 is the first column in this index, and the index type is BTREE.
• The non-unique index (col2) on col3 in the third row. It indicates that col3 is the second column in this index, and the index type is BTREE.

 Create Index for Existing MySQL Table

MySQL best practices recommend creating an index at the same time the table is created. However, an index may also be added to an existing table. The instructions below explain how to add an index to an existing MySQL table.

1. Create a new table by entering the following statements:

CREATE TABLE example2 (
col1 INT PRIMARY KEY,
col2 VARCHAR(20),
col3 VARCHAR(20),
col4 VARCHAR(20)
);

2. Next, show the current index for example2:

SHOW INDEXES FROM example2;

The system displays a single index for the primary key. The example2 table has an index on the col1 column, even though we did not explicitly use the CREATE INDEX statement. When you define a column as a primary key within the CREATE TABLE statement, MySQL automatically creates an index on that column.

3. Add an index for col2 and col3 with the following statement:

CREATE INDEX index1 ON example2 (col2,col3);

4. Display the indexes for example2:

SHOW INDEXES FROM example2;

The system displays the primary key index, plus col2 and col3 listed in index1.

Remove Index from MySQL Table

To remove indexes in MySQL, use the DROP INDEX statement. The syntax is:

DROP INDEX [index_name] ON [table_name};

For example, to drop the index1 index from the example2 table, run the following:

DROP INDEX index1 ON example2;

Verify the change with:

SHOW INDEXES FROM example2;

The output shows that the index1 index has been removed.

Troubleshooting Indexing Issues in MySQL

Efficient indexing is crucial for optimizing query performance and ensuring a responsive MySQL database. Troubleshooting missing, redundant, or incorrect index types significantly enhances data retrieval speed and overall database efficiency. The following sections show common MySQL indexing issues and adequate solutions.

Missing Index

When an index is missing, MySQL queries must read through all the rows in the table to find the relevant ones. This is inefficient, especially for large tables with millions of rows.

The first step to fix a missing index is to determine which queries are running slowly. To find low-performing queries, monitor query performance and look at the query execution time.

The EXPLAIN statement shows how MySQL executes a query, including whether it uses an index and which index it uses. If the execution plan shows a "full table scan" (where the type column is ALL), it means the query is not using an index.

For example:

EXPLAIN SELECT * FROM example2 WHERE col2 = 'value';

The type: ALL, shows the query is performing a full table scan, indicating a missing index. Create an index on col2 to optimize the query by allowing MySQL to quickly locate the rows that match the condition col2 = 'value'.

To create an index on the col2 column, use the following SQL statement:

CREATE INDEX idx_col2 ON example2(col2);

Redundant Index

Redundant indexes occur when multiple indexes exist on the same columns or when indexes are created unnecessarily. These redundant indexes increase storage and maintenance overhead, making database operations less efficient.

To fix redundant indexes, use the SHOW INDEXES command to list all indexes on a table and look for redundancy. Redundant indexes often have the same column or set of columns indexed multiple times. For example:

SHOW INDEXES FROM example2; 

The output shows all indexes on the example2 table, including details such as index names, columns indexed, and whether the index is unique. Carefully examine the output to identify indexes that serve the same purpose, such as multiple single-column indexes on columns that are frequently queried together.

For instance, the example above shows two indexes on col2 and two indexes on col3.

Once you have identified unnecessary indexes, drop them to free up space and reduce maintenance overhead.

Incorrect Index Type

Using the wrong index type for your query workload leads to poor performance. For instance, using a hash index for range queries or sorting operations significantly degrades performance, as hash indexes are not designed for these operation types.

On the other hand, using B-tree indexes for exact match queries is not always necessary, as hash indexes provide faster lookups in those scenarios.

To avoid mistakes, learn the index types and their purpose. Also, determine the query types your application frequently executes. For example, if your application often performs exact match lookups, hash indexes are suitable. If it performs range queries or sorting, B-tree indexes are more appropriate.

Conclusion

This article explains how to create, manage, and remove MySQL indexes and elaborates on common indexing issues.

Next, learn how to improve MySQL database performance.