Logo

Understanding The MySQL CREATE INDEX Statement

Try this guide with our instant dedicated server for as low as 40 Euros

mysql create index

Imagine a library with thousands of books. Trying to find a book by going through all the shelves would be a nightmare.

This is where experienced Library goers will opt for using library catalogs. These catalogs contain the exact location of books. Librarians and users can get the book directly without having to go through all the shelves. 

Similarly, indexes in MySQL act like these library catalogs. They organize the data in the database, speeding up searches and running database operations efficiently.

In this tutorial, we will discuss the CREATE INDEX statement and how to improve query response with it. We will start with an introduction to the CREATE INDEX statement and then see the major steps in the process of creating an index.

Table Of Content

  1. What is the CREATE INDEX Statement?
  2. How to Create an Index in MySQL
  3. Conclusion
  4. FAQs

What is the CREATE INDEX Statement?

The CREATE INDEX statement in MySQL is a powerful tool for optimizing database performance. 

It allows users to create special data structures called indexes on specific columns within a table. These indexes act like shortcuts, enabling the database engine to locate data much faster than searching through every single row in the table.

The Basic Syntax

The basic syntax of the CREATE INDEX statement is as follows:

mysql>  CREATE INDEX [index_name] ON [table_name] ([column_name1], [column_name2]);

Here,

[index_name]: This is a user-defined name for the index.

ON [table_name]: This specifies the table in which you want to create the index.

column_name1, column_name2: Names of columns to be indexed.

Now that you understand the basics of the CREATE INDEX statement, let us discuss how to create a MySQL index.

How to Create an Index in MySQL 

Creating an index in MySQL is a crucial step in improving database performance.

However, before we move over to the details, let us take a quick look at the prerequisites. 

The Prerequisites to Creating Index in MySQL  

Before diving in, ensure you have the following: 

  • A MySQL server installed on your system
  • A user account with sudo or administrative privilege.
  • Access to a command line / terminal window

How to Create an Index in a New Table

We recommend you follow the steps below to create an index and a table simultaneously.

Step #1: Log in to the MySQL Shell

Open a terminal window and log into the MySQL shell.

# mysql -u username -p

Replace the username with your MySQL username and enter the password when prompted.

Step #2: Create a New Database

Execute the following statement to create a new database named testdb

MariaDB > CREATE DATABASE testdb;

Step #3: Switch to the New Database

Now that you created a new database, you can switch the SQL session to this newly created database using the USE statement.

MariaDB > USE testdb;

Step #4: Create a Table with Columns and an Index

Next, create a table with columns and indexes using the following CREATE TABLE statement: 

MariaDB > CREATE TABLE testdb (

              col1 INT PRIMARY KEY,

              col2 INT NOT NULL,

              col3 INT NOT NULL,

              col4 VARCHAR(20),

              INDEX (col2, col3)

              );

Here,

CREATE TABLE testdb: This statement creates a new table named testdb.

col1 INT PRIMARY KEY: Defines col1 as an integer column that will be the primary key for the table.

col2 INT NOT NULL: Defines col2 as a non-null integer.

col3 INT NOT NULL: Defines col3 as a non-null integer.

col4 VARCHAR(20): Defines col4 as a variable character column that can store up to 20 characters.

INDEX (col2, col3): Creates an index on the col2 and col3 columns.

use test database

Consider another example. 

Here, we will create a table named RedSwitches with four columns and indexes with the following statement: 

MariaDB >  CREATE TABLE Redswitches (

               col1 INT PRIMARY KEY,

               col2 INT NOT NULL,

               col3 INT NOT NULL,

               col4 VARCHAR(20),

               INDEX (col2, col3)

               );

create table RedSwitches

How to Add an Index to an Existing MySQL Table

Now that you have learned to create a table and index simultaneously, let us see how to add an index to an already existing table. 

MySQL best practices recommend creating an index and table simultaneously. 

However, you can also add an index to an existing table. For this, follow the steps below to add an index to an already existing table. 

Let us consider the above example where we have already created a table named Redswitches2.

Next, to display the current indexes of the Redswitches2 table, execute the following statement:

MariaDB > SHOW INDEXES FROM Redswitches2;

create table redswitches2

The statement displays the current indexes for the Redswitches2 table.

Now that you are familiar with the existing indexes in Redswitches2 table, let us add indexes using the CREATE INDEX statement.

MariaDB > CREATE INDEX index1 ON Redswitches2 (col2, col3);

create index1 on redswitches2

Once you’ve created the index, use the SHOW INDEXES command to verify.

MariaDB > SHOW INDEXES FROM Redswitches2;

The statement displays all the indexes in the table, including their names and the columns they cover.

show indexes in redswitches2

Conclusion

Mastering the use of the CREATE INDEX statement in MySQL is essential for optimizing your database performance. 

By strategically creating indexes on your tables, you can significantly reduce query execution times and improve data retrieval efficiency. 

Understanding the different types of indexes and their appropriate applications allows you to tailor your indexing strategy to your specific database needs.

Whether working with small datasets or large, complex databases, effectively utilizing the CREATE INDEX statement in MySQL ensures your applications run smoothly and efficiently.

FAQs

Q. Why should we create an index in MySQL?

Creating an index in MySQL aims to improve the speed and efficiency of data retrieval operations. Indexes enable the database engine to locate and access data more quickly, thereby enhancing overall query performance.

Q. How to create an index on a MySQL table?

You can create an index on a MySQL table using the CREATE INDEX statement. The basic syntax is: CREATE INDEX index_name ON table_name (column_name);

Q. Can I create an index on multiple columns in MySQL?

Yes, you can create a multi-column index in MySQL. The syntax is:

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

This type of index can enhance the performance of queries that filter or sort data based on multiple columns.

Q. Explain different types of indexes available in MySQL?

MySQL supports several types of indexes, including:

Primary Key: A unique index is automatically created for the primary key column.

Unique Index: Ensures all values in the indexed column are unique.

Full-text Index: helps full-text searches.

Spatial Index: helps spatial data types.

Regular Index: Also known as a non-unique index.

Q. How does a primary key differ from a regular index?

A primary key is a unique index that ensures the column’s values are distinct and does not allow NULL values. In contrast, a regular index can include duplicate and NULL values.

Q. When should I use a unique index in MySQL?

You must use a unique index to ensure that all values in the indexed column are distinct. This is useful for columns that require unique entries, such as email addresses or usernames.

Q. How can I remove an index from a MySQL table?

To remove an index from a MySQL table, use the DROP INDEX statement. The syntax is: DROP INDEX index_name ON table_name;

This command deletes the specified index from the table.

Q. Can indexes impact the performance of write operations?

Yes, while indexes improve read performance, they can negatively impact write operations (inserts, updates, and deletes) as the database needs to update the indexes whenever the indexed data changes.

Q. What is a full-text index, and when should I use it?

A full-text index is designed for full-text searches, allowing efficient search capabilities for large text columns. Use a full-text index when performing complex search queries on text data.

Q. How can I check if an index exists on a MySQL table?

You can check if an index exists on a MySQL table by querying the INFORMATION_SCHEMA database:

SELECT * FROM INFORMATION_SCHEMA.STATISTICS 

WHERE TABLE_SCHEMA = ‘your_database_name’ AND TABLE_NAME = ‘your_table_name’;

This query will list all indexes on the specified table.

 

Try this guide with our instant dedicated server for as low as 40 Euros