How to Create an Index in MySQL:
A Complete Guide

As databases continue to grow and evolve, data storage and management become increasingly complex and demanding. The processing of data requires more time, but it is critical to have the possibility to retrieve the necessary data quickly and efficiently.

The most common option for resolving this challenge is using indexes that directly refer to the necessary data within the database tables. This article will explore the types, characteristics, and applications of indexes in MySQL.

Why use indexes in MySQL?

The main issue with querying large tables that don't have indexes is that the query needs to scan the entire table until it finds the records that meet the conditions. This often takes a lot of time and resources. As tables become larger and more complex, queries become more problematic. Indexes help resolve this issue by creating order in a table - a data structure with references to the necessary information.

Indexes are exceptionally helpful in the following scenarios:

• When queries include ORDER BY for sorting records.
• When queries include range conditions like BETWEEN, <, <=, >, or >=.
• When queries contain JOIN clauses (indexes should be on the columns used in joins).
• When queries deal with text-based searches.
• When all columns in a query are part of an index, allowing the query to use the index instead of scanning the target table.

On the other hand, indexes are separate database objects, and the more columns are included in an index, the more space it consumes. Additionally, indexes increase table structure complexity and can complicate database design and administration. Therefore, implementing an index is not a universal solution and should be considered carefully for each specific case.

There are situations where it's better to avoid indexes:

• With small tables.
• With frequently updated tables.
• With columns not used in WHERE clauses.

Next, let's examine how to create indexes in MySQL.

Syntax for MySQL CREATE INDEX statement

In MySQL, indexes are created using the CREATE INDEX statement. The basic syntax of the query is:

CREATE INDEX index_name  
ON table_name (column1, column2, ...);  

The parameters are:

• index_name - the name of the created index, which must be unique within a table but can be repeated across tables in the database.
• table_name - the name of the table where the index is created.
• column1, column2, ... - the column or the list of columns included in the index.

When an index is no longer needed, it can be removed using the DROP INDEX statement:

DROP INDEX index_name 
ON table_name;  

Our next step is to explore the different types of indexes supported in MySQL.

Types and examples for MySQL CREATE INDEX

MySQL indexes can be created on both single and multiple columns. These indexes can be either unique or non-unique, depending on the specific requirements of each use case. In this section, we will explore different types of indexes and demonstrate how they are used in real-world scenarios.

To illustrate our cases, we will use the popular MySQL test database, sakila, and dbForge Studio for MySQL, a comprehensive IDE for MySQL and MariaDB databases.

Single-column indexes

A single-column index is the simplest and most common index type in MySQL. It is created on a single column in a table and is used to speed up queries that filter results based on values from that specific column.

The syntax for creating a single-column index on a table is:

CREATE INDEX index_name  
ON table_name(column_name);

To create a single-column index on the last_name column in the actor table from the sakila database, use the query:

CREATE INDEX idx_actor_last_name  
ON actor(last_name);  

To verify that the index was created, use the SHOW INDEX command:

SHOW INDEX FROM actor;  

Composite indexes

In many cases, you might need to include multiple columns in an index, especially if you use those columns frequently together in queries, particularly for large tables with many columns. A multiple-column index (a composite index) combines values from multiple columns into a single index, allowing MySQL to find data based on several criteria at once.

Such indexes are often a better solution than several single-column indexes as they ensure faster query execution, reduce storage space, and minimize the number of disk I/O operations.

To create a composite index in MySQL, you need to include all the necessary columns (up to 16) in the CREATE INDEX statement as follows:

CREATE INDEX index_name 
ON table_name (column1, column2, ...);

Suppose we frequently query the rental table using both customer_id and rental_date. A composite index on these columns can speed up such queries. Without an index, the query takes 0.124 seconds.

SELECT 
*
FROM rental
WHERE customer_id = 5
ORDER BY rental_date DESC;

We create a composite index on the rental table:

CREATE INDEX idx_customer_rental_date 
ON rental (customer_id, rental_date);

Repeat the search, and we can see the result delivered in 0.004 seconds

Unique indexes

A unique index in MySQL ensures that values in an indexed column or multiple columns are unique across all rows in a table. It does not allow any duplicates in a column even if it is not a primary key.

You can create a unique index when you create a table, or you can add it later using the ALTER TABLE statement. It can be a single-column index or a composite index (the combination of records in several columns must be unique then). A unique index allows NULL values in indexed columns.

To create a unique index, use the following query:

CREATE UNIQUE INDEX index_name 
ON table_name (column1, column2, ...);

In our test case, the customer table contains customer details, including their email addresses. We want to use the email addresses in the unique index, as these values must be unique for each customer.

CREATE UNIQUE INDEX idx_unique_customer_email 
ON customer (email);

Without an index established, the search takes 0.117 seconds:

SELECT
  *
FROM customer
WHERE email = 'WADE.DELVALLE@sakilacustomer.org';

When we rerun the search having a unique index on the column, we can get the results delivered within 0.004 seconds:

Another example involves a unique composite index. In the rental table, each inventory item can only be rented once at a given time by a specific customer. Searching without an index brings us results within 0.116 seconds.

SELECT
  *
FROM rental
WHERE inventory_id = 1498
AND customer_id = 64
AND rental_date = '2005-05-31 00:46:31';

We can create a composite unique index on inventory_id, customer_id, and rental_date:

CREATE UNIQUE INDEX idx_unique_rental 
ON rental (inventory_id, customer_id, rental_date);

Performing the same search now delivers results in 0.003 seconds.

Full-text indexes

A full-text index in MySQL allows you to perform text searches in InnoDB tables (starting from version 5.6) with CHAR, VARCHAR, and TEXT columns. You can use full-text search functions like MATCH() and AGAINST() to query records efficiently within these indexes.

Full-text indexes simplify searching for textual data in articles, blogs, product descriptions, forum posts, chat applications, FAQ posts, documents, logs, job descriptions, and so on. As such resources are frequently updated, it is essential to re-index such columns regularly.

The query to create a full-text index is:

CREATE FULLTEXT INDEX index_name
ON tbl_name(column1, column2,...);

Let's create a full-text index in the film_text table:

CREATE FULLTEXT INDEX idx_film_description
ON film_text (description);

To search for films containing the phrase 'emotional drama' in their descriptions, use the following query:

SELECT
  film_id,
  title,
  description
FROM film_text
WHERE MATCH(description) AGAINST('emotional drama' IN NATURAL LANGUAGE MODE);

As you can see, results are delivered within 0.016 seconds:

Spatial indexes

Spatial indexing in MySQL allows you to search for spatial data, such as points, lines, and polygons, and efficiently retrieve and filter results based on their geometrical properties.

By applying spatial indexes, you can execute queries that involve spatial data much faster, perform complex data analysis, and handle larger volumes of spatial data much more efficiently.

Let us see how this works in a real-world scenario. As the sakila database does not contain spatial data, we have modified it and created a new test table called it customer_temp. It contains the data from the customer table with additional latitude and longitude columns and a column named location of type GEOMETRY, which stores the geographical data (latitude and longitude). The POINT data type is used to represent the location, and the values stored in that column are populated from the values of existing latitude and longitude columns.

CREATE TABLE sakila.customer_temp_new (
  customer_id smallint UNSIGNED NOT NULL DEFAULT 0,
  store_id tinyint UNSIGNED NOT NULL,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  email varchar(50) DEFAULT NULL,
  city varchar(50) DEFAULT NULL,
  latitude decimal(10, 6) DEFAULT NULL,
  longitude decimal(10, 6) DEFAULT NULL,
  location POINT
);

Let's say we want to find customers within a 50 km radius of a specific point. We'll use ST_Distance_Sphere to calculate the distance between points.

SELECT
  customer_id,
  first_name,
  last_name,
  city,
  latitude,
  longitude,
  ST_ASTEXT(location) AS location
FROM sakila.customer_temp
WHERE ST_DISTANCE_SPHERE(location, ST_GEOMFROMTEXT('POINT(129.7222 33.1592)')) <= 50000;

The query takes 0.105 seconds to complete.

Now, let us see how it works with the spatial index added to the location column.

We use the below statement to add a spatial index to our test table.

ALTER TABLE sakila.customer_temp 
ADD SPATIAL INDEX (location);

Rerun the query. The result delivery takes 0.004 seconds.

MySQL CREATE INDEX vs. ALTER TABLE ADD INDEX

In the above scenarios, we demonstrated the usage of both CREATE INDEX and ALTER TABLE ADD INDEX statements to create indexes in MySQL. However, these two approaches have different contexts and use cases. Below, we have compiled a table to review the specific features and differences in usage of these two statements.

Common mistakes when using indexes

As we already defined, indexing is a powerful optimization technique for query performance. However, improper use can slow performance down, increase resource usage, and cause additional complexity in maintaining databases. Let us consider the most common mistakes when using indexes and define how to avoid them.

Too many indexes on a table

Over-indexing can affect the standard UPDATE, INSERT, and DELETE operations significantly because MySQL has to maintain the indexes whenever data is modified. While indexes improve query performance, each index requires storage space, and the more indexes you set, the more space is taken, which impacts the performance.

To avoid this trouble, analyze your queries and define which of them require more indexes. Limit the number of existing indexes and remove the unnecessary ones. dbForge Studio for MySQL offers its Query Profiler feature that helps you tune MySQL queries and investigate query performance issues. In particular, the EXPLAIN plan provides you with in-depth stats data for each particular query, so you can define bottlenecks and resolve them effectively.

Incorrect order of columns in composite indexes

The order of columns in the index can affect query performance. Indexes are optimized for queries that match the columns in the same order they appear in the index. Incorrect ordering can lead to poor performance for queries that don't utilize the index effectively.

Ensure that columns used in the WHERE, JOIN, or ORDER BY clauses appear first in the index. The column that contains the most unique values must come first in the index. Analyze your queries with the EXPLAIN plan to understand which columns are used and adjust your index ordering accordingly.

Absence of appropriate index usage monitoring

Indexes often become less effective or irrelevant over time. As a result, the system must manage these unused indexes, leading to unnecessary overhead and a degrade in overall performance.

The recommendations remain the same: regularly review all existing indexes to determine which are actively used and which have become obsolete. Unused indexes should be removed, while active ones should be optimized for better performance.

Creating and using MySQL indexes with dbForge Studio

We used dbForge Studio for MySQL to demonstrate the use of different indexes. The queries were composed using the MySQL Code Editor and executed against the database. The Studio offers numerous coding assistance features, including code autocompletion, formatting, debugging, and more. Additionally, we showcased how dbForge Studio helps with query analysis, enabling users to detect issues and optimize performance. However, these are just a few examples of its powerful capabilities. As a comprehensive IDE, dbForge Studio provides a complete toolset for database development, management, and administration, covering everything from writing code to version control.

One of dbForge Studio's most valuable features is its visualization capability, allowing users to perform many database tasks in a graphical interface, including index management. The Table Designer helps you create and manage indexes with just a few clicks.

• 1. In the Database Explorer, right-click the desired table and select Open Editor. Navigate to the Indexes tab to see all indexes associated with the table. You can create new indexes or remove existing ones from this tab.

• 

• 2. Click Add non-unique index or Add unique index, depending on your goal. Specify the index name, type, and columns to be included. dbForge Studio generates an SQL script in real-time to create the index, which you can use or modify as needed.

• 

• 3. To delete an index, select the necessary index and click Remove Index.

• 

• 4. Click Apply Changes to finalize the task of creating or removing the index.

Conclusion

Indexes are powerful tools that make working with databases much more ordered and efficient. Without them, retrieving data from large databases quickly and accurately would be nearly impossible. That's why it is essential to understand the different types of indexes available in MySQL (or any other database management system), how they function, how to create them, and how to maintain them for optimal performance.

dbForge Studio for MySQL is a toolset designed to simplify database management in MySQL and MariaDB. Its features help you perform all daily tasks, providing a single solution for various database challenges. An intuitive GUI allows you to switch to visual mode, making configuration and execution easier without manual coding.

You can download dbForge Studio for MySQL and test its capabilities in real-world scenarios. A fully functional 30-day free trial is available, so you can explore its full potential under actual workloads.

FAQ