Composite index and column order in SQL

Introduction

A composite index helps the database find data faster when searching using multiple columns together. The order of columns in the index matters because it affects how quickly the database can use the index.

When you often search or filter data using two or more columns together.

When you want to speed up queries that use multiple columns in the WHERE clause.

When sorting or grouping data by multiple columns.

When you want to avoid creating many single-column indexes and save space.

When queries use columns in a specific order for filtering or sorting.

Syntax

SQL

CREATE INDEX index_name ON table_name (column1, column2, ...);

The order of columns in parentheses matters; the database uses the index starting from the first column.

You can include two or more columns to create a composite index.

Examples

Creates a composite index on last_name and first_name in that order.

SQL

CREATE INDEX idx_name ON employees (last_name, first_name);

Creates a composite index on order_date and status to speed up queries filtering by these columns.

SQL

CREATE INDEX idx_date_status ON orders (order_date, status);

Sample Program

This creates a table and a composite index on order_date and status. The EXPLAIN QUERY PLAN shows how the database uses the index for the query filtering by both columns.

SQL

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  status VARCHAR(20)
);

CREATE INDEX idx_date_status ON orders (order_date, status);

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE order_date = '2024-01-01' AND status = 'shipped';

Important Notes

The database can use the composite index efficiently only if the query filters on the first column or the first columns in order.

If you search only by the second column in the index without the first, the index might not be used.

Composite indexes can improve performance but also take extra space and slow down inserts or updates.

Summary

Composite indexes speed up searches using multiple columns together.

The order of columns in the index is important for how the database uses it.

Use composite indexes when queries filter or sort by multiple columns in a specific order.