- Trending Categories
- Data Structure
- Operating System
- C Programming
- 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
152 Lectures 16 hours
87 Lectures 5.5 hours
A composite index is an index that is used on multiple columns. It is also known as a multiplecolumn index.
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
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)
- When should I use a composite index in MySQL?
- Finding Duplicate Rows in MySQL (Composite Key)?
- Making slow queries fast using composite indexes in MySQL
- ALTER TABLE to add a composite primary key in MySQL?
- Composite Key in RDBMS
- How to form a composite key to be unique in MySQL?
- How to identify composite primary key in any MySQL database table?
- Composite Design Pattern in C++
- Describe concept of composite depreciation.
- Remove index from a MySQL table
- What is a composite attribute in DBMS?
- MySQL index on column of int type?
- Create index on create table in MySQL?
- Create index of three columns in MySQL?
- Integrate using the composite trapezoidal rule in Python