Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
MySQL Composite Index
A composite index is an index that is used on multiple columns. It is also known as a multiplecolumn index.
Features
Let us see the features −
MySQL allows the user to create a composite index which can consist of up to 16 columns.
The query optimizer uses the composite indexes for queries which will test all columns in the index.
It can also be used for queries which will test the first columns, the first two columns, and so on.
If the columns are specified in the right order in the index definition, a single composite index can be used that would speed up certain kinds of queries on the same table.
Creating Composite Index
Let us see how a composite index can be created, during the creation of a table. It can be done using the below statement −
CREATE TABLE table_name ( c1 data_type PRIMARY KEY, c2 data_type, c3 data_type, c4 data_type, INDEX index_name (c2,c3,c4) );
In the above statement, the composite index consists of three columns c2, c3, and c4.
A composite index can also be added into an existing table using the ‘CREATE INDEX’ statement. Let us see how this can be done
Query
CREATE INDEX index_name ON table_name(c2,c3,c4);
If there is a composite index on (c1,c2,c3), then the user would have indexed search capabilities on one the below mentioned column combinations −
(c1) (c1,c2) (c1,c2,c3)
7K+ Views
