In this tutorial, we will learn about the composite index in MySQL which is also called a multiple column index. The name itself describes what it is and how it is created.
We will start with the introduction of the composite index and then go through some important points like where can we use the composite index and its syntax. And finally, we will see some examples to better understand its functionality. So, let’s get started!
Also read: MySQL Invisible Index
Introduction to MySQL Composite Index
When we create an index on multiple columns, it becomes a composite index. For example, we can create an index which consists of two columns- first name and last name.
MySQL allows up to 16 columns to be included in the composite index.
Note that, the composite index is also called a multiple-column index. Both are the exactly same thing!
Multiple-column indexes in MySQL can be used for queries that test all of the index’s columns as well as queries that just test the first column, the first two columns, the first three columns, and so on. Sounds confusing, right? We will understand it briefly when we see its syntax.
Syntax of MySQL Composite Index
To create a composite index, the following statement is used-
INDEX index_name (list_of_columns);
---for example
CREATE TABLE table_name (
a1 data_type PRIMARY KEY,
a2 data_type,
a3 data_type,
a4 data_type,
INDEX index_name (a2,a3,a4)
);
---add composite index on existing table
CREATE INDEX index_name
ON table_name(a2,a3,a4);
Code language: SQL (Structured Query Language) (sql)Here, you can give any meaningful name to the index. Note that, multiple types of queries on the same database can be sped up by a single composite index if the columns are defined in the correct sequence in the index definition.
In the above syntax, we have created an index on three columns- a2,a3 and a4.
This index will be useful for queries that join, filter, and sometimes select data from certain fields. It will also help queries that use the left-most subsets of those composite columns.
That means you can benefit from the indexing on one of the following column combinations-
(a2)
(a2,a3)
(a2,a3,a4)Code language: SQL (Structured Query Language) (sql)For example,
You can search like-
SELECT * FROM table
WHERE a2=val2;
SELECT * FROM table
WHERE a2=val2 AND a3=val3;
SELECT * FROM table
WHERE a2=val2 AND a3=val3 AND a4=val4;Code language: SQL (Structured Query Language) (sql)If the columns do not make up the leftmost term of the index, MySQL is unable to use the index to do lookups. For example, the following queries cannot use the composite for lookups:
SELECT * FROM table
WHERE a3=val3 AND a2=val2;
SELECT * FROM table
WHERE a2=val2 AND a4=val4;Code language: SQL (Structured Query Language) (sql)If you look at the second query, the set of the a2 and a4 doesn’t make a left-most subset in the composite index. So, it can not use the composite index.
Examples of MySQL Composite Index
To demonstrate the composite index, we will use the following table.
Now we will create an index on the student’s table over the name, dept and subjects column.
CREATE INDEX stud ON students(name,dept);Code language: SQL (Structured Query Language) (sql)Here, we can use the stud index for the lookups in the queries that specify the student name as well as the combination of student name and department.
Therefore, we can use the above index on the following queries-
SELECT * FROM students WHERE name=' ';
SELECT * FROM students WHERE name=' ' AND dept= ' 'Code language: SQL (Structured Query Language) (sql)Let’s write a query with the EXPLAIN clause to verify if the query uses the index.
1. Write a query to fetch the records where the name is Bruce.
EXPLAIN SELECT * FROM students WHERE name='Bruce';Code language: SQL (Structured Query Language) (sql)
As you can see, the key name is displayed in the possible_keys column.
Now we will search based on both columns specified in the index- name and dept.
2. Write a query to fetch the records where the name is Bruce and the department is CS.
EXPLAIN SELECT * FROM students WHERE name='Bruce' AND dept='CS';Code language: SQL (Structured Query Language) (sql)
3. Write a query to fetch the records where the name is Bruce and the depart meant is either CS or Language
EXPLAIN SELECT * FROM students WHERE name='Bruce' AND (dept='CS' OR dept='Language');Code language: SQL (Structured Query Language) (sql)
As you can see here, the query has used the index stud and returned the result.
4. Now we will write a query which will search the records by only department instead of name or combination of name and department.
EXPLAIN SELECT * FROM students WHERE dept='CS';Code language: SQL (Structured Query Language) (sql)
As you can see here, the query is not using the index because the specified list of columns in the where clause doesn’t make a left-most subset of the columns specified in the definition of the index.
When Should We Use the Composite Index?
When using queries that can benefit from a composite index, you should use one. A composite index with the following format:
index( column_A, column_B, column_C )Code language: SQL (Structured Query Language) (sql)A query that uses those fields for joining, filtering, and occasionally selecting, will profit from this. It will also help queries that employ the left-most subsets of those composite columns. Thus, the aforementioned index will also respond to requests for
index( column_A, column_B, column_C )
index( column_A, column_B )
index( column_A )Code language: SQL (Structured Query Language) (sql)However, it won’t (at least not directly; if there aren’t any better indices, it could assist in part) support queries that need
index( column_A, column_C )Code language: SQL (Structured Query Language) (sql)You can read the whole thread on StackOverflow.
Conclusion
In this tutorial, we have learned about the composite index. We have learned its syntax, examples and when to use it. It is very important to understand how composite indexing works.
References
MySQL official documentation on composite index/multiple column indexes.