MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
1PLs Company - #1Payday.Loans Agency - Loans online and near me $100-$2500 (Same Day)
Powered by MySQL
 
Home / Indexes / Normal Indexes or Keys / Question No: 142

How to create multi column normal index or key in MySQL?

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?

Answer No: 142

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);

Related MySQL FAQs to the Above FAQ

How-to-create-normal-index-or-key-in-MySQL How to create normal index or key in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2023  www.mysqlfaqs.net
All rights reserved.