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)
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 indexsome_table
– The name of the table with columns being indexedsome_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)
);
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.
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.
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.
Next you should also read
June 30, 2020
The DROP statement is a simple method to remove entire tables from your databases. It provides several…
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…
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…
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…
Author
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.