Home / Indexes / Normal Indexes or Keys / Question No: 142

I want to create multi column INDEX (KEY) in my table without any constraint. How to create multi column index or key without constraints in MySQL? Any help please?

Index without constraints is also known as "normal index, ordinary index, or non-unique index". To create multi-columns normal index is simple as unique index and primary key index. Since MySQL allows to create normal key or index on single column as well as on multi-columns (combination of columns) of the table, you can create multi columns normal index using CREATE TABLE, ALTER TABLE and CREATE INDEX statements provided by the MySQL:

You can create multi column Normal Index while creating your table. The following is an example to create multi column Normal Index using CREATE TABLE statement:

CREATE TABLE buyers ( 
 buyer_id INT UNSIGNED NOT NULL, 
 first_name CHAR(19) NOT NULL, 
 last_name CHAR(19) NOT NULL, 
 age SMALLINT NOT NULL, 
 post_code SMALLINT NOT NULL, 
 INDEX idx_name_age (first_name,last_name,age));

You can create multi column Normal Index after creating your table or when you decide to add it later. In this case, you will have to alter your table. The following is an example to create multi column Normal Index using ALTER TABLE statement:

Syntax to alter the table to add an unique index:

ALTER TABLE tablename ADD INDEX name_of_index[optional] (columns_to_index);

Add multi-column normal index or key altering the "buyers" table:

ALTER TABLE buyers ADD INDEX idx_name_age(first_name,last_name,age);

Also, you can create multi column Normal Index without altering your table. This is possible through CREATE INDEX statement. Take the following example to create multi-column Normal Index using CREATE INDEX statement:

Syntax to create multi-column normal index using CREATE INDEX statement:

CREATE INDEX name_of_index[optional] ON tablename (columns_to_index);

Add multi column normal index in the "buyers" table using CREATE INDEX statement:

CREATE INDEX idx_name_age ON buyers(first_name,last_name,age);