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:-

• MySQL Add PRIMARY Key as Index
• MySQL Add UNIQUE Index
• MySQL Add ORDINARY Index
• MySQL Add FULLTEXT Index

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.

MySQL Add UNIQUE Index

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.

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.

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"),
 (2,"Henry",5000,"Apperals");

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

MySQL Add ORDINARY Index

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

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.

MySQL Add FULLTEXT Index

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.

READ MORE

• MySQL create table if not exists
• MySQL ADD FOREIGN KEY
• MySQL get column names

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