Composite Index in SQL
FREE
Overview
Indexing in SQL is a powerful tool that provides faster access to data entries through the use of quick lookup tables. Composite indexes, also known as multiple-column indexes, provide even faster access to data entries by utilizing multiple columns within an index.
This provides faster query times in any table at the cost of extra space. Composite indexes utilize data structures like B-trees or hashtables to store pointers of multiple lookup tables for each column, which helps in providing quick access to data entries.
What is Composite Index in SQL?
- SQL composite indexes are indexes with multiple columns within them that provide faster query times.
- The order of columns within the SQL composite index is important in providing access to columns.
Indexes in SQL make columns faster to query by creating quick lookup tables that store pointers to data entries in a table. For example, in the table illustrated below, for one to search for a data entry where Subject A's mark is the highest would take a large number of traversals. If we instead index the table on Subject A, we can do the same operation in minimum time.
For large databases, accessing data entries sequentially may take up a large amount of time, especially if the queried entry is toward the end of the table. SQL provides the use of columns as indexes, which essentially creates a separate table that stores the column in a sorted order along with a pointer to where the data is stored within the database. The following diagram illustrates how indexes have been created that point to the original data entries. We have taken a table with six columns and a few data entries and have indexed the table on Roll No.
Composite Indexes in SQL are also known as multi-column indexes and they allow the use of multiple columns to form an index. Composite indexes are similar to standard indexes as they both store a sorted "table" of pointers to data entries. Composite indexes however can also store additional sorted pointers to other columns. Composite indexes in SQL allow even faster access to data entries as it utilizes multiple columns to sort through data faster.
From the above diagram, we can observe how composite indexes are stored in the database. As you can see, the different columns in the index are sequentially connected and as such follow a hierarchical order from left to right. Thus, we cannot access the columns in the right direction as the pointers to those are inaccessible. Composite indexes in SQL use b-tree, BRIN, GiST, GIN, etc structures to store pointers to columns and can consist of up to 32 columns. They provide faster access to data at the cost of extra space that is used to store these index structures.
Syntax
To create a composite index during table creation, the following syntax is used :
To add a composite index to an existing table, the following syntax can be used :
While creating a composite index in SQL, the column order is very important. This is because of the structure in which the composite index is stored. As such, multi-column indexes have a hierarchical order from left to right. Thus, for the above syntaxes, we have search capabilities on the following column combinations :
Thus, a column combination like (c2,c4) cannot be used to search or query in the table.
How to Create a SQL Composite Index ?
Let us take the example of the Students table we've used in the previous sections. It consists of six columns : Roll No, First name, Last Name, Subject A, Subject B, and Total.
We can add a composite index ID that contains the columns First name and Last name. This creates an index ID which contains both columns linked by pointers. We can use the following query :
This composite index can then be used in queries in the following ways :
-
Select all elements where the First name is Olivia
-
Select all elements where the First name is Ryan and the Last name is Wilson
The composite index ID is used while filtering in the above examples. This can be verified using the EXPLAIN command with SELECT.
The composite index ID is not used in the following example as the index cannot access the LastName column directly.
-
Select all elements where the Last name is Martin
Performance of Multi-Column Indexing
Multicolumn indexing is objectively faster at traversing through data. Adding standard indexes to tables increases their efficiency manyfold during read operations.
Multicolumn indexing provides even faster read speeds, especially when filtering using multiple columns. This is especially useful in larger datasets with millions of entries, where sorting through data can take an exceptionally large amount of time.
This of course comes with its setbacks as adding large indexes to tables causes performance penalties during INSERT, UPDATE, and DELETE operations along with taking up extra space in the form of the data structure that stores the indexes.
FAQ
Q: When to Use Composite Index?
A: Composite indexes** are especially useful when dealing with large datasets with a large number of entries. They are also helpful when dealing with data entries with repeated values. However, it is important to note that adding a composite index will take up extra space and it also comes with performance penalties during INSERT, UPDATE, and DELETE operations. Thus, the composite index provides faster READ times at the cost of other operations, and hence, it is better to use composite indexes if the priority is to READ data.
Q: How Does Column Order Affect Composite Index Usage?
A: As previously mentioned, columns within a composite index follow a hierarchy, and thus not every column can be accessed directly. We can access a column only if all columns to its left are accessed. Thus, column order plays an important role in composite index usage. Furthermore, to maximize performance, the first column in the index must be the most selective, or in other words, should be present in the most number of queries. This helps the query optimizer filter through data faster and therefore increases efficiency.
Conclusion
- Composite indexes in SQL are indexes with multiple columns within them.
- Composite indexes in SQL are stored in structures like b-tree, BRIN, GiST, GIN, etc.
- Composite indexes in SQL are faster at reading data when compared to standard indexes.
- Composite indexes provide faster READ times at the cost of storage and performance penalties during INSERT, UPDATE, and DELETE.
- The first column in a composite index should be the most selective to increase efficiency.