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.

Next you should also read