How to Create or Add an Index in MySQL With Examples

August 31, 2020

Introduction

MySQL is a database application that organizes data in tables, which in turn have values organized in rows and columns. In MySQL, 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 and is very inefficient.

This guide will show you how to create an index in MySQL using the CREATE INDEX statement.

CREATE INDEX Statement to add an index in mysql

Prerequisites

  • An existing MySQL installation
  • A MySQL user account with root or admin privileges
  • Access to a command line / terminal window:
    • In Linux, click Activities > Search > Terminal
    • In Windows, right-click Start > Command Line (or Windows PowerShell)

What is a MySQL Index? Like the index of a book, a MySQL table index keeps track of where different values are located. This speeds up data queries, but at the cost of disk space and reads/writes.


MySQL Create Index Syntax

A CREATE INDEX statement in MySQL uses the following format:

mysql> CREATE INDEX [some_index] ON [some_table] ([some_column],[other_column]);
    • some_index – The name of the index
    • some_table – The name of the table with columns being indexed
    • some_column, other_column – The names of the columns to be indexed

Note: The following is an example of a MySQL CREATE INDEX statement:
mysql> CREATE INDEX index1 ON table1 (column1,column2);


MySQL Create Index in New Table

To create an index at the same time the table is created in MySQL:

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

mysql -u username -p

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

mysql> CREATE DATABASE mytest;
Query OK, 1 row affected (0.01 sec)
mysql; USE mytest;
Database changed

3. Next, create columns in the table, and index them:

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

Creating a MySQL table

The command creates a table named example, populates it with 4 columns, and adds indexes for column 2 and column 3.

4. Next, display a list of the indexes from the table:

mysql; SHOW INDEXES FROM example;

You should see an index for PRIMARY, COL2, and COL3. Even though PRIMARY wasn’t specified, MySQL automatically created an index for the primary key.

MySQL output that displays a MySQL index


Note: An index cannot be used to create a primary key. However, when a table is created with a primary key, MySQL creates an index called PRIMARY for the primary key values in that same table. This is also called a clustered index.


Add Index to 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 command:

mysql> CREATE TABLE example2 (
col1 INT PRIMARY KEY,
col2 VARCHAR(20),
col3 VARCHAR(20),
col4 VARCHAR(20)
);
Query OK, 0 rows affected (0.25 sec)

Note: The above-mentioned command doesn’t include instructions to index the new table.


2. Next, show the current index for example2:

mysql> SHOW INDEXES FROM example2;

The system should display a single index for the primary key.

Displaying a single entry MySQL index

3. Next, add an index for col2 and col3 by entering the following command:

mysql> CREATE INDEX index1 ON example2 (col2,col3);

How to create mysql index

4. Display the indexes for example2:

mysql> SHOW INDEXES FROM example2;

The system should now display the primary key index, plus col2 and col3 listed in index1.

Additional entries in the MySQL index

Conclusion

You should now have an understanding of how to create an index in a MySQL database.

Next, consider tuning the performance of your MySQL database to improve its efficiency.

Aleksandar Kovačević
Aleksandar Kovacevic is an aspiring Technical Writer at phoenixNAP. With a background in both design and writing, he aims to bring a fresh perspective to writing for IT, making complicated concepts easy to understand and approach.
Next you should read
How to Drop a Table in MySQL
June 30, 2020

The DROP statement is a simple method to remove entire tables from your databases. It provides several...
Read more
How to Install and Configure MySQL on a Windows Server
April 23, 2020

The article provides many images that guide you through each individual step of a MySQL installation on...
Read more
How to Rename a Column in MySQL
March 30, 2020

This article offers two slick methods to rename a column in a MySQL table. Use ALTER TABLE command combined...
Read more
How to Remove Duplicate Rows in MySQL
March 5, 2020

When adding data to a MySQL database, you may end up with duplicate rows. This article shows you how to find...
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.