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