MySQL Create Index Guide: How to Add and Optimize Indexes for Performance

Have you thought of how big a deal is to create an index in MySQL? 

Using it enough vs. not using it is like night and day. So, if you want your users to have a smooth-sailing app or website, adding table indexes is a must. 

But are you still new to this? Then, this article is for you. Here’s a nutshell of what we’re going to discuss: 

• What is an Index in MySQL? 
• Types of MySQL Indexes
• How to Create an Index in MySQL  
• Alternative Ways to Create a MySQL Index 
• How to Optimize Index Usage in MySQL 
• Common Errors and Troubleshooting in MySQL Indexing 

Let’s dive in. 

It Could Have Been Better… 

Let’s set the stage a bit. 

Imagine: It’s almost the end of the month, and the support lines are busy. The tech support team glares at you while they calm the users. 

So, you chose to face your screen. 

But the screen is more terrifying than those burning eyes. Your MySQL server is almost at a halt. CPU cores are peaking at 100% and disk I/O is insane. 

What could be wrong? 

When you pinpoint the culprit, a complex query of a monthly report eats CPU cores and bangs the disk with table scans. Table scans are a sign of a missing index and summarizing a gazillion rows with them is a nightmare. 

Your day could have been better. That’s why indexes are a big deal. 

But what actually is it? 

What is an Index in MySQL? 

A MySQL index is a data structure that boosts query performance by allowing the database to find rows faster. Instead of scanning every row, MySQL uses indexes like a book’s table of contents to jump straight to the records you need. 

But MySQL will not add one for you. You have to create one or more indexes for your database tables. You can create an index in MySQL using CREATE INDEX, CREATE TABLE, and ALTER TABLE. We will discuss each of these with examples later. 

Remember: A table without an index is like a library with no catalog. You’d have to check every book on every shelf just to find the one you need. 

For example, without an index, a query like this: 

SELECT * FROM stores WHERE city = 'SYDNEY'; 

Would push MySQL to scan every row in the stores table until it finds the ones located in Sydney. But if there’s an index, it jumps right at the rows where the city is Sydney and displays them. 

Indexes have types and you need to be familiar with them: 

• Primary Key Index – Controls unique identification of each row – a must for most tables. 
• Unique Index – Enforces uniqueness and allows multiple NULL values. 
• Composite Index – Allows fast searches on multiple columns. 
• Full-Text Index – Speeds up text-based searches. 
• Multi-Valued Index (MVI) – Optimized for JSON arrays. 
• Spatial Index – Allows fast searches on location-based queries. 

Let’s learn more about how to use each and when in the next section. 

Types of Indexes in MySQL 

Think of indexes as different types of keys. Some unlock one door, others work for multiple doors at once, and some let you find words inside a book instead of flipping through every page. 

Primary Key Index 

A Primary Key Index uniquely identifies each row in a table. It’s automatically created when you define a PRIMARY KEY column. A table can only have one primary key. Take a look at the CREATE TABLE code below: 

CREATE TABLE anime_titles 
(anime_title_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
 anime_title_name VARCHAR(150) NOT NULL); 

The anime_title_id is declared a primary key and an index should be available after creation. 

A primary key index is like your mobile number. That number is unique to you and is needed for people to reach you. 

Unique Index 

A Unique index prevents duplicates in one or more columns. If you try to insert values that already exist, you’ll get an error. 

For example, consider a unique index for the anime_titles table earlier. The anime_title_name should be unique. So, if you try to insert the anime One Piece twice in the table, you’ll get a unique key constraint error. 

But you may wonder how is this different from primary key indexes. It’s simple: 

• A PRIMARY KEY can’t have NULL and is mandatory. 
• A UNIQUE INDEX can have NULLs and isn’t required. 

A unique index is like your email. It should be unique, but not necessarily the only way to contact you. 

Composite Index 

A Composite Index is an index with multiple columns to boost searches involving them. 

For example, you can set the lastname and firstname columns of an employee table. So, searches in the WHERE clause using those columns will be faster. 

Another good thing about the composite index is you can use the same index for the leftmost column(s). So, if your WHERE clause includes lastname only, you can still enjoy the fast search using the same composite index. But it might not help if you use firstname in the WHERE clause. The point here is to use the leftmost column. 

If you have a 3-column composite index, you can use the first two columns in the search, but not the first and third or second and third. But MySQL will still use the index if you include all 3 columns in a different sequence than the one defined in the index. 

So, let’s say you have a composite index for lastname, firstname, and middlename. If you use all 3 columns in the WHERE clause but in a different sequence, you can relax because your query will still run fast. 

A composite index is like a key chain with multiple keys – useful when unlocking doors in order. 

Full-Text Index 

Are you looking for specific words in a large text column? Then, a full-text index is your friend. It speeds up text searches, especially for long articles, descriptions, or messages. It works with a WHERE clause that includes MATCH() and AGAINST(). 

Check out a SELECT statement with a full-text query below: 

It looks for matches in the film descriptions with the word Epic. 

A full-text index is like Google Search. It finds keywords instantly instead of scanning every page. 

Multi-Valued Index (MVI) 

Introduced in MySQL 8.0, a multi-valued index improves searches on JSON arrays. The usual index has one index record for each data record (1:1). But an MVI can have multiple index records for a single data record (N:1). You can’t use a multi-valued index on other data types aside from JSON. 

You can use JSON array columns on video tags, properties or attributes of insurance policies, and more. Then, you can search for these using MVI. 

A multi-valued index is like a grocery shopping app. It finds multiple items at once instead of searching aisle by aisle. 

Spatial Index 

A spatial index for boosting queries on geometric and geographic data stored in MySQL’s spatial data types, such as POINT, LINESTRING, and POLYGON. 

This is good for spatial queries, such as finding nearby locations or checking if a point is within a region. So, if you’re building an app with mapping features, use the MySQL spatial data types and this index type. 

How to Create an Index in MySQL 

There are a few ways to create an index using MySQL code. We will discuss them all here. 

Basic Syntax for Creating a MySQL Index 

Creating MySQL indexes for starters uses CREATE INDEX. Check out the syntax below: 

CREATE [index_type] INDEX index_name ON table_name(column1, column2, columnN); 

Let’s explain this a bit: 

• index_name is the name of the index. 
• table_name refers to the table where the index will be created. 
• [index_type] is optional and can refer to FULLTEXT, UNIQUE, or SPATIAL. 
• column1, column2, columnN refers to the columns to index. A basic syntax uses one column. A composite index will need a few columns separated by commas. 

Examples 

Below are examples using different index types already discussed. 

1. Index Actor table by Last Name 

The sakila database has an actor table. Check out the index on last name: 

USE sakila; 
CREATE INDEX idx_actor_last_name ON actor(last_name); 

The name of the index is idx_actor_last_name. And actor is the table and last_name is the indexed column. 

2. Creating a Unique Composite Index on an Existing Table 

You can add a unique and composite index using CREATE INDEX: 

USE sakila; 
CREATE UNIQUE INDEX UK_staff_name ON staff(last_name, first_name); 

It’s a unique index because of the keyword UNIQUE. And it’s a composite index because two columns, last_name and first_name, are the index keys. 

3. Creating a Full-text Index 

The query using a full-text index earlier uses the following index: 

USE sakila; 
CREATE FULLTEXT INDEX IDX_film_text_description ON film_text(description); 

The keyword FULLTEXT indicates that the new index will be a full-text index. 

Creating an Index During Table Creation 

Another way to create an index is during table creation. So, you will do it within the MySQL CREATE TABLE block. Below is the syntax: 

CREATE TABLE table_name ( 
   <column_definition> 
   [FULLTEXT | UNIQUE | SPATIAL] INDEX index_name(column1, column2, columnN,...) 
); 

Here are the takeaways from the code above: 

• table_name is the name of the table. 
• <column_definition> is where you define your columns, their types, and more. 
• [FULLTEXT | UNIQUE | SPATIAL] is optional but when indicated can refer to either a full-text, unique, or spatial index. 

Examples 

Below are examples of MySQL CREATE TABLE with index command: 

1. Create a Unique Index During Table Creation 

The employees table will be created with a unique index on lastname and firstname. 

CREATE TABLE employees ( 
    id INT PRIMARY KEY, 
    lastname VARCHAR(100), 
    firstname VARCHAR(100), 
    UNIQUE INDEX UK_name (lastname, firstname) 
); 

2. Creating a Spatial Index Within CREATE TABLE 

Below is a sample of preparing a MySQL table for storing coordinates and indexing it: 

CREATE TABLE locations ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(100), 
    coordinates POINT NOT NULL, 
    SPATIAL INDEX idx_coordinates (coordinates) 
); 

Adding an Index to an Existing Table 

You can also add an index using ALTER TABLE. Check out the syntax: 

ALTER TABLE table_name  
ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name(column1, column2, columnN,..) 

A few points on this syntax: 

• table_name is the name of the table you’re going to index 
• [FULLTEXT | UNIQUE | SPATIAL] is optional but when indicated can refer to either a full-text, unique, or spatial index. 
• index_name is the unique name of the index. 
• (column1, column2, columnN,..) is the column or columns you want to index. 

As an alternative to CREATE INDEX, below is a sample: 

ALTER TABLE sakila.film_text 
ADD FULLTEXT INDEX IDX_film_text_description (description); 

You will get the same result as the CREATE FULLTEXT INDEX example earlier. The rest of the CREATE INDEX samples are also convertible to ALTER TABLE ADD INDEX. 

Alternative Ways to Create an Index in MySQL 

Not everyone likes writing SQL commands. You might prefer visual tools. MySQL GUI tools and database managers are also capable of creating indexes. So, instead of typing the commands, you type less and click more. 

Using GUI tools offers benefits: 

• No need to memorize indexing commands. 
• Error-free indexing. 
• Easy index management. 

In this section, you’ll learn about the visual tools you can use for indexing. 

How to Create an Index with dbForge Studio for MySQL 

dbForge Studio for MySQL from Devart is one of the best choices for MySQL GUI tools. While you can do all the MySQL commands in its SQL editor, you can use its Table Designer to create and modify indexes. You can also have several options to show indexes in both code and GUI. 

Check out the steps below using the Table Designer: 

1. From the Database Explorer, connect to your MySQL server 
2. Navigate to your desired table and right-click it. 
3. Click Open Editor to open the table in the Table Designer.

4. Click the Indexes tab. 
5. Click Add non-unique index (or Add unique index if you want a UNIQUE index). 
6. A new row will appear in the list of indexes. Make sure it’s selected. 
7. Give your index a name and change other index options, like Invisible, Unique, etc. 
8. Add a column you want to index. Add more if you need a composite index. Change the options for each, like Sort Order and Key Length.

9. When done, click Apply Changes. 

From here, your new index is ready for use. 

How to Create an Index Using MySQL Workbench 

MySQL Workbench is a free tool provided by Oracle for MySQL databases. Use this tool to manage database objects, including indexes. 

Below are the steps to create an index: 

1. In the Navigator -> Schemas, navigate to the table you want to index and click the wrench icon. 
2. Click the Indexes tab. 
3. There’s an empty line in the list of indexes. Double-click the box under Index Name and name your index. Then, give it an index type. 
4. In the Index Columns, mark the columns you wish to add to the new index. Then, optionally fill in the Index Options. 
5. Click Apply. 

Below is an illustration of the above steps: 

Creating an Index Using Hosting Platforms 

MySQL hosting platforms like Google Cloud and Amazon Web Services (AWS) have querying tools online. Google Cloud uses Cloud SQL Studio while AWS has the RDS Query Editor. This is meant for light querying. So, you can use the CREATE INDEX, CREATE TABLE, and ALTER TABLE commands to create indexes. 

Meanwhile, Microsoft Azure has Azure Data Studio, and using a MySQL extension you can manage your Azure MySQL databases. From there, you can create indexes. 

Other providers like FreeDB offer MySQL hosting and you can use phpMyAdmin to manage your databases. See it below: 

Below is the web page for creating an index for a customers table in phpMyAdmin: 

How to Optimize Index Usage in MySQL 

Though it’s easy to create indexes using code or GUI tools, you can’t just keep on adding them. Indexes are powerful, but too many can slow things down. Think of them like bookmarks—useful, but if every page is marked, finding the right one takes longer. 

Below are some tips to optimize index usage. 

Best Practices for Indexing 

When do you use indexes and when do you avoid them? 

• Use indexes for frequently searched columns. What columns do you use in the WHERE clause and joins? These are candidates for indexing. Table scans will result if you don’t have indexes on these columns. But remember that the keyword here is frequent. 
• Avoid indexing columns with low selectivity. If a table column seldom appears in the WHERE clause and joins, you may not need to index it. Though you need to examine the query with tuning techniques first before you decide. Adding indexes more than what is necessary will make write operations slow. This includes inserts, updates, and deletes. 
• Remove unused or duplicate indexes. Over time, some indexes will not be used due to changes in your queries. So, DROP these indexes. And if you find duplicate indexes, remove one of them. These indexes will add to storage and memory, and it will make write operations slow. 

Adding indexes are good but you have to make sure MySQL is using it in queries. How do you know MySQL will use your index? Read on to the next section to find out. 

Checking Index Usage 

Your new index will only be good if MySQL will use it. So, how can we know? 

MySQL has this nifty command called EXPLAIN. It will tell you if MySQL used your new index for your query. But there’s more: It’s a tool for query performance tuning. 

Check out the basic EXPLAIN syntax below: 

EXPLAIN SELECT statement; 

To check for index usage in SELECT statements, just prefix the EXPLAIN keyword. Note that you can also use EXPLAIN for INSERT, UPDATE, and DELETE. 

Let’s have some examples. I’ll be using dbForge Studio for MySQL, but the EXPLAIN command is available in any MySQL GUI or even the Command Line Interface (CLI). We will also use the sakila database because most of us are familiar with it. 

Interpreting EXPLAIN Results for Index Usage 

The actor table has an index on the last_name column. I’m going to create a new index for last_name and first_name columns. So, I’ll drop the existing one first and then create a composite index. 

DROP INDEX idx_actor_last_name ON actor; 

ALTER TABLE sakila.actor 
ADD INDEX idx_actor_name (last_name, first_name); 

Note the name of the new index (idx_actor_name). 

Filtering with the Leftmost Column 

Now, let’s EXPLAIN the following code: 

EXPLAIN 
SELECT a.actor_id, a.last_update  
FROM actor a 
WHERE a.last_name = 'WILLIAMS'; 

It’s a simple query. Note that the index is for both last_name and first_name. But the WHERE clause only used the last_name column. See the results below and let’s check if the index we created was used: 

MySQL used the index we created because our WHERE clause uses the leftmost index column (last_name). Check the possible_keys and key columns. It shows the name of the index we created (idx_actor_name). It also has a type ref, which means MySQL performed an index seek. 

Not Using the Leftmost Index Column in the WHERE Clause 

But what if we used the first_name column in the WHERE clause? Check the code below: 

EXPLAIN 
SELECT a.actor_id, a.last_update 
FROM actor a 
WHERE a.first_name = 'SUSAN'; 

Then, check how the results have changed: 

Since first_name is not the leftmost column in the index, using it in the WHERE clause results in a table scan (type = ALL). Also, the possible_keys and key columns are both null. This will result in a slower query execution. So, note this fact in using composite indexes, and avoid a type = ALL, especially on large result sets. 

But there’s more. 

The Covering Index 

Let’s say we query the columns last_name and first_name instead of actor_id and last_update. And our WHERE clause uses the first_name column (not the leftmost). Will MySQL use table scan (type = ALL)? 

The answer is NO. 

But why? 

Because this time, MySQL will use an index scan (type = index). Since your SELECT columns are included in the index (last_name, first_name), MySQL doesn’t need to scan the table. The index will suffice. This is called a covering index. Check the results below: 

As seen above, our new index was used even if the WHERE clause uses a column that is not the leftmost. But instead of a table scan, it uses an index scan. This is better than a table scan, but index seek (type = ref) is the fastest. 

We learned that EXPLAIN can reveal index usage. Look for the type, possible_keys, and key columns in the EXPLAIN results. It tells you if your index is used by MySQL. We also dealt with a composite index and learned about table scan, index seek, and index scan. Your goal is to achieve index seek in query tuning because this is the fastest. Avoid table scans because this is the slowest. 

Removing an Index 

To remove unused and duplicate indexes, you need to use DROP INDEX. Below is the syntax: 

DROP INDEX index_name ON table_name; 

• index_name is the name of the index you want to delete. 
• table_name is the name of the table where the index you want to delete is found. 

Common Errors and Troubleshooting in MySQL Indexing 

Knowing how and when to create an index doesn’t mean you’re always in a fairyland. It’s not always a happily ever after. Expect problems and errors along the way especially if you’re new. But you’ll get the hang of it with practice. 

Below are some problems and errors you may encounter along the way. 

Syntax Errors 

If you’re using the CLI or your GUI tool has limited to no syntax checker and intellisense, you will experience the following from time to time: 

• mixed up the keywords, 
• spelled the keywords wrong, 
• use non-existent columns and tables, 
• arranged the statement in the wrong way. 

Solution: 

• Use a GUI tool like MySQL Workbench or dbForge Studio for MySQL and avoid the problems above. 

Duplicate Index Names 

You may get the error: 

ERROR 1061 (42000): Duplicate key name 'idx_email' 

MySQL is telling you that there’s already an index named idx_email. 

Solution: 

• Rename your index. Make sure it’s unique 
• Drop the duplicate index name and create the index with the same name. 

Different Index Names But With the Same Keys 

With this problem, you will create an index and it will be successful. Since the index name is different, you won’t notice at first. But, upon inspecting them, the columns used are the same as another index with a different name. 

Solution: 

Check existing indexes – Check the indexes by their names and columns included before creating a new index. 

Performance Issues Due to Indexing 

While indexes can speed up things on your query, it can also do the opposite if you don’t do it right. Below are the performance culprits: 

Too Many Indexes 

Too many indexes require more storage and RAM. MySQL also updates all indexes due to your INSERT, UPDATE, and DELETE. High-volume table writes per second with many indexes will perform poorly. 

Solution: 

• Reduce the number of indexes by removing duplicates and removing the ones with low selectivity (See Best Practices above). 
• If the indexes are crucial for fast report output, consider having a data warehouse and use that for reporting. Then, remove the indexes for reporting purposes in the transactional database. This will offload the transactional database and boost things further. 

Unused Indexes 

Earlier, we discussed about EXPLAIN and use it to check for index usage. Use it to see if there are unused indexes. Unused indexes take disk space and get updated even if MySQL doesn’t use them for fast SELECT statements. 

Solution: 

• Use EXPLAIN to search for unused indexes. 
• DROP the index once found. 

Takeaways 

In this article, you learned about a few ways in creating an index for a table. You also learned about EXPLAIN for checking index usage and tuning query performance. Then, you learned about common errors and problems newbies can make and the solution was presented. 

This article uses dbForge Studio and MySQL 9.1. If you also want this GUI tool like me, you can download dbForge Studio for MySQL. Or you can try it as a part of dbForge Edge – Devart’s multi-database solution. 

Just like a messy filing system can slow down an office, bad indexing can slow down a database. Keep it clean, efficient, and well-structured! 

Tags: mariadb, mysql, mysql add index, mysql alter table add index, mysql create index, MySQL CREATE INDEX Statement, mysql create table with index, mysql index creation Last modified: April 14, 2025