Add a Column to Index in MySQL Table

MySQL – Add Column to Index

A MySQL index stores values from one or more table columns in a structure that helps MySQL locate matching rows without scanning every row. In this MySQL Tutorial, you will learn how to add a single column or multiple columns to an index, verify the index, and check whether a query uses it.

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.

ALTER TABLE table_name ADD INDEX index_name (column_name);

The statement creates a non-unique secondary index named index_name for column_name. Choose a descriptive index name, such as idx_students_section, so that the indexed table and column are easy to identify.

Example to add a column to the INDEX in MySQL

Consider the following students table.

whose INDEX is as shown below:

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.

ALTER TABLE students ADD INDEX nameIndex (section);

The column section is indexed with the index name nameIndex, which is the name used in the SQL statement above.

You can refer to this index by its name when viewing, altering, or dropping the index.

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

Verify the new MySQL index with SHOW INDEX

Run SHOW INDEX after creating the index. The result identifies the index name, indexed column, sequence within a composite index, uniqueness, and other index metadata.

SHOW INDEX FROM students;

You can also query INFORMATION_SCHEMA.STATISTICS when you need index details in a form that can be filtered or used by an administrative script.

SELECT
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'students'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

Create a MySQL index with CREATE INDEX

MySQL also supports CREATE INDEX. For a regular secondary index, it provides the same practical result as ALTER TABLE ... ADD INDEX.

CREATE INDEX index_name
ON table_name (column_name);

The earlier students example can therefore be written as follows with a more descriptive index name.

CREATE INDEX idx_students_section
ON students (section);

Add multiple columns to a composite MySQL index

Place more than one column inside the parentheses to create a composite index. Column order matters because MySQL can use the leftmost columns of a composite index for lookups. Choose the order from the query patterns you actually run rather than arranging columns alphabetically.

ALTER TABLE students
ADD INDEX idx_students_section_name (section, name);

This index can support conditions that begin with section, including a condition on section alone or on both section and name. It is generally not equivalent to a separate index that begins with name.

SELECT *
FROM students
WHERE section = 'A'
  AND name = 'Ravi';

Add a column to an existing MySQL composite index

MySQL does not provide syntax that appends a key part to an existing index in place. To add another column to an existing composite index, inspect the current definition, drop that index, and create it again with the complete ordered column list.

For example, to change idx_students_section from an index on section to an index on section and name, use one ALTER TABLE statement:

ALTER TABLE students
DROP INDEX idx_students_section,
ADD INDEX idx_students_section_name (section, name);

Check for duplicate or overlapping indexes before doing this. If another index already begins with the same columns in the same order, an additional index may be unnecessary.

Create a unique index for values that must not repeat

Use a unique index only when duplicate non-NULL values must be rejected by the database. A unique index is a data-integrity rule as well as an access path, so it should not replace an ordinary index unless uniqueness is a real requirement.

ALTER TABLE students
ADD UNIQUE INDEX uq_students_admission_number (admission_number);

Create a prefix index for a long text column

For an indexable string column, MySQL can index only a leading portion of each value. A prefix index can reduce index size, but it can also be less selective than indexing the complete value. Select the prefix length after examining the data.

ALTER TABLE students
ADD INDEX idx_students_address_prefix (address(30));

For nonbinary string types, the prefix length is expressed in characters. Prefix indexes are not appropriate when queries need efficient matching on characters that occur only near the end of the value.

Check whether MySQL uses the added index

An index existing on the table does not guarantee that MySQL will choose it for every query. Use EXPLAIN with the real query to inspect the optimizer’s plan. Review the possible_keys, key, rows, and Extra columns in the result.

EXPLAIN
SELECT *
FROM students
WHERE section = 'A';

MySQL may prefer a table scan when a table is small, when a condition matches a large share of rows, or when another access path is estimated to cost less. Test representative queries and data rather than judging an index only by whether it appears in SHOW INDEX.

Remove an unused MySQL index

Every secondary index uses storage and must be maintained during inserts, updates, and deletes. Remove an index when it is confirmed to be unnecessary and no constraint depends on it.

ALTER TABLE students
DROP INDEX nameIndex;

The equivalent DROP INDEX form is:

DROP INDEX nameIndex ON students;

Choose columns for a useful MySQL index

• Start with queries that are slow or run frequently, and inspect them with EXPLAIN.
• Consider columns used in selective WHERE predicates and join conditions.
• For composite indexes, match the column order to equality, range, sorting, and grouping patterns in the target queries.
• Avoid creating an index on every column. Redundant indexes consume space and add write overhead.
• Check existing primary, unique, and composite indexes before adding another one.
• Test index creation on a representative environment before altering a large or heavily used table.

Frequently asked questions about adding MySQL indexes

How do I add an index to a MySQL table column?

Run ALTER TABLE table_name ADD INDEX index_name (column_name); or CREATE INDEX index_name ON table_name (column_name);. Then confirm the result with SHOW INDEX FROM table_name;.

How do I add a column to an existing MySQL index?

Drop the existing index and create it again with the complete composite column list. Preserve the intended column order because MySQL can use the leftmost prefixes of a composite index.

What is the difference between CREATE INDEX and ALTER TABLE ADD INDEX?

Both statements can create a secondary index on an existing MySQL table. ALTER TABLE is convenient when applying several table changes together, while CREATE INDEX expresses a single index-creation operation directly.

Can I create a MySQL index on multiple columns?

Yes. List the columns in order, such as ADD INDEX idx_name (column1, column2). Design the order around the target queries because an index beginning with column1 does not generally replace an index beginning with column2.

Does adding an index always make a MySQL query faster?

No. The benefit depends on query structure, data distribution, selectivity, table size, and the indexes already available. Indexes also add storage and write-maintenance costs, so validate the query plan with EXPLAIN.

MySQL index tutorial QA checklist

• The example index name matches the name used in its SQL statement.
• Single-column and composite-index syntax use valid MySQL forms.
• The composite-index explanation states that column order and the leftmost prefix matter.
• The tutorial distinguishes adding a new index from adding a column to an existing index.
• Unique indexes are described as enforcing uniqueness, not merely improving lookup speed.
• SHOW INDEX is included for verification and EXPLAIN is included for query-plan checking.
• The tutorial warns against redundant indexes and notes their storage and write costs.