MySQL ADD INDEX Statement

Overview

In this tutorial, we will learn about MySQL ADD INDEX statement. The index can be added to existing tables using the ALTER TABLE statement. We can use the ALTER TABLE with the ADD INDEX clause that specifies the index column/s to be added.

An index will assist MySQL database Server to find table rows more quickly and easily.

https://www.testingdocs.com/mysql-table-indexes/

Syntax

The general syntax for the statement is a follows:

ALTER TABLE <table_name> ADD INDEX index_name (index_column/s)

Example

Let’s an index to City table from the world MySQL database.

mysql> ALTER TABLE City ADD INDEX pop_index (Population);

Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0

MySQL ADD INDEX

To confirm the change, use SHOW CREATE TABLE to see the table’s structure:

mysql> SHOW CREATE TABLE City \G

*************************** 1. row ***************************
Table: City
Create Table: CREATE TABLE `city` (
`ID` int NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT ”,
`CountryCode` char(3) NOT NULL DEFAULT ”,
`District` char(20) NOT NULL DEFAULT ”,
`Population` int NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
KEY `pop_index` (`Population`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

Notice that the KEY pop_index ad the index column name added to the table structure.

SHOW INDEX statement

To check the details of the index, we can use the SHOW INDEX statement as shown below:

https://www.testingdocs.com/mysql-show-index-statement/

mysql> SHOW INDEX FROM City \G

*************************** 3. row ***************************
Table: city
Non_unique: 1
Key_name: pop_index
Seq_in_index: 1
Column_name: Population
Collation: A
Cardinality: 3897
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
3 rows in set (0.02 sec)