MySQL – Add Column to Index

Index is used to access rows of a table with increased performance.

Consider that you are having a large table and has a query in which you fetch the rows based on a column. If the column is not PRIMARY KEY but you use that quite often for filtering the rows, then it is better to add the column to the index.

To add a column to the index of table, following is the syntax of the SQL Query.

</>
Copy
ALTER TABLE table_name ADD INDEX index_name (column_name);

Example to add a column to the INDEX in MySQL

Consider the following students table.

MySQL table data

whose INDEX is as shown below:

MySQL SHOW INDEX FROM TABLE

Now we shall add one more column of students table to the INDEX. Let us say, the column, section.

Using the syntax mentioned earlier, we prepared the following SQL Query and we shall run it in mysql.

</>
Copy
ALTER TABLE students ADD INDEX nameIndex (section);
MySQL ALTER TABLE table_name ADD INDEX index_name (column_name)

The column section is added to the INDEX with index_name as sectionIndex.

In future, you may access this particular index with the name sectionIndex.

Let us verify, if this has been added to the INDEX of the table.

MySQL - Showing updated index of a table