How to Create or Add an Index in MySQL With Examples

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.

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)

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

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)
);

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.

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)

2. Next, show the current index for example2:

mysql> SHOW INDEXES FROM example2;

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

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

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

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.

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.