Summary -

In this topic, we described about the below sections -

Note :- ALTER TABLE with ADD INDEX is supported by MYSQL but not in Oracle or SQL Server. If we try to execute on SQL, it throws an error.

Why to add index?

Sometimes, we might understand the table retrieval is getting slow day by day. The reason might behind this is, may be due to the data increasing in the table day by day. So adding an index to the table is a solution to improve the retrieval speed of the data.

ALTER TABLE with ADD INDEX fulfils the above requirement.

How to add index?

ALTER TABLE with ADD INDEX statement is used to add index on existing table in database.

Syntax -

ALTER TABLE table_name
 ADD INDEX index_name (column1, column2, …, columnN);
  • table_name - Specifies the table_name on which the index is going to create.
  • Index_name - Specifies the newly creating index_name
  • column1, column2, .., column - Specifies the columns that are part of index

Example -

Scenario – Adding index on table.

Requirement - Add index idx_eid on emp_id column in employee_details table. The query was as follows - 

ALTER TABLE employee_details
ADD INDEX idx_eid (emp_id);

By executing above query, we can crate index idx_eid on the employee_details table.

If the table is not optimized, we can get an error 'ALTER TABLE ADD INDEX is supported only with memory optimized tables.

We can add multiple non-clustered indexes on one table. Whereas we can add only one clustered index on one table.