MySQL add index to existing table

In this article, we will see how to add indices to an existing table. Indexes are used when we want to find the values of a column rapidly.

Table of contents:-

Assume that we have an existing table sale_details with many rows of data in it. We will be using this table across examples. To view the columns of the sale_details table, we will execute:

DESC sale_details;

MySQL Add PRIMARY Key as Index

This section will create index on one of the columns, say id column in sale_details table by making it a primary key. In MySQL, whenever we make a primary key, it is considered to be an index by default.

ALTER TABLE sale_details ADD PRIMARY KEY (id);

Let us verify if the index has been added by executing:

SHOW INDEX FROM sale_details;

The output in image_2 shows that the id column now has been indexed, and these values cannot be NULL or DUPLICATE now.


This section will see how to create a UNIQUE index on a MySQL table’s column. Unique index enforces uniqueness on the column values. There can be more than one column with the unique index on the table.

Read More  MySQL get column names

We are adding a unique index to the column sale_person_name

ALTER TABLE sale_details ADD UNIQUE sale_person_unique_index (sale_person_name);

Let us verify if the index has been added by executing:

SHOW INDEX FROM sale_details;

Index named sale_person_unique_index is created on column sale_person_name. Trying to insert more than one row with the same name will now lead to errors.

Read More  MySQL Delete Duplicate Rows but keep one

We will try to add two rows with the same name, “Henry,” and observe the response from the MySQL server.

 INSERT INTO sale_details (id,sale_person_name,no_products_sold,sales_department) 
 VALUES(1,"Henry",2000,"Kichten Essentials"),

While inserting the second row MySQL server threw the error: Error Code: 1062. Duplicate entry ‘Henry’ for key ‘sale_details.sale_person_unique_index’


In this section, we will be adding an ORDINARY index to the sales_department column.

Read More  MySQL For Loop Example
ALTER TABLE sale_details ADD INDEX sale_index (sales_department);

Verify if the index got added by executing:

SHOW INDEX FROM sale_details;

sale_index is an index on the sales_department column.


In this section, we will be adding a FULLTEXT index to sale_person_name. Note that the FULLTEXT index can only be applied to CHAR, VARCHAR, or TEXT columns. 

ALTER TABLE sale_details ADD FULLTEXT sale_person_fulltext_index (sale_person_name);

Verify if the index got added by executing:

SHOW INDEX FROM sale_details;

Output in image_5 shows that the FULLTEXT index is successfully created on sale_person_name.


We hope this article helped in adding foreign keys to the MYSQL table. Good Luck!!!

You need to add a widget, row, or prebuilt layout before you’ll see anything here. 🙂

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top