Beacon Robotics frequently filters employees by department_id and hire_date together, to find who joined a given department in a given window. The previous lesson indexed single columns, this one covers indexing that combination.
Learning Objectives
- Create a composite (multi-column) index, and explain what order its columns should be declared in.
- Explain the leftmost-prefix rule, and why an index on (a, b) doesn't speed up a query filtering on b alone.
- Know the real difference between a UNIQUE constraint and a UNIQUE index, and where NULL handling in a composite unique index differs by database.
Composite Index
One index, multiple columns
Leftmost Prefix
Usable from the first column onward
Unique Index
Enforces uniqueness, and speeds lookups
Creating a Composite Index
The CREATE INDEX statement itself, with multiple columns listed in parentheses, is identical across all six databases. What differs is how that index actually gets used, covered next.
CREATE INDEX idx_employees_dept_hire ON employees(department_id, hire_date);
The Leftmost-Prefix Rule
A composite B-tree index is sorted first by its leftmost column, then by the next column within each group of matching values, and so on. That means the index can only be searched starting from its leftmost column inward, a query filtering on a later column alone has to skip over that sorted structure entirely, which the engine can't do efficiently.
This is a structural consequence of how every one of these six databases physically builds a composite B-tree, not a quirk of any single one, it's genuinely the same rule everywhere.
| Query Filters On | Can Use (department_id, hire_date) Index? |
|---|---|
| department_id = X | Yes, uses the leftmost column alone. |
| department_id = X AND hire_date = Y | Yes, uses both columns in order. |
| hire_date = Y (alone) | No, skips the leftmost column entirely. |
Column Order Matters
Put the column most often filtered on its own, or with the most distinct values, first. A (department_id, hire_date) index serves a department_id-only query and a department_id-plus-hire_date query equally well, but a (hire_date, department_id) index would only serve the first of those if hire_date were queried alone just as often, decide the order from how the columns actually get queried, not alphabetically or by creation order.
UNIQUE Constraint vs UNIQUE Index
A UNIQUE constraint is a rule declared at the schema level, Module 8 covered it. Behind that declaration, the database builds a UNIQUE index to actually enforce and check it. A UNIQUE index created directly with CREATE UNIQUE INDEX enforces the exact same rule, without a separately named constraint object, functionally the two are nearly identical, the real difference is how each is removed later, DROP CONSTRAINT for the first, DROP INDEX for the second.
CREATE UNIQUE INDEX idx_employees_email ON employees(email);
Composite Unique Indexes and NULLs, by Database
| Database | NULL in a Composite Unique Index |
|---|---|
| MySQL / MariaDB / PostgreSQL / SQLite / Oracle | Each NULL is treated as distinct from every other NULL, so multiple rows sharing the same non-NULL values, but NULL in one indexed column, don't violate uniqueness. |
| SQL Server | NULL is treated as a comparable value for uniqueness, so two rows with identical values across every indexed column, including matching NULLs, DO violate the index. Only one such combination is allowed. |
This is the same split Module 8 covered for a single-column UNIQUE constraint, extended to a composite index: SQL Server is the one database here that counts NULL as a real, comparable value rather than as "unknown, never equal to anything, including itself".
Try It Yourself
Create a composite index on employees covering department_id and last_name together, on PostgreSQL.
CREATE INDEX idx_employees_dept_lastname ON employees(department_id, last_name);
Frequently Asked Questions
Can one composite index replace several single-column indexes?
Sometimes, if queries always filter in that leftmost order, yes. But a composite index on (a, b) still can't help a query filtering only on b, so a single-column index on b alone is still needed for that case.
Does column order in a UNIQUE index change what counts as a duplicate?
No, uniqueness is enforced on the full combination of values regardless of the order they're declared in. Column order still changes which queries can use the index efficiently via the leftmost-prefix rule, that part does matter, just not for what counts as a duplicate.
Common Beginner Mistakes
Expecting (department_id, hire_date) to speed up a query that only filters hire_date
It won't, the leftmost-prefix rule means the index is only usable starting from department_id. A query filtering hire_date alone needs its own index on hire_date, or a composite index with hire_date as the leftmost column instead.
Assuming SQL Server's NULL handling in a unique index matches every other database
It doesn't. SQL Server allows only one row with a given combination of NULLs in a unique index, while MySQL, MariaDB, PostgreSQL, SQLite, and Oracle all allow unlimited rows with NULL in the same indexed column, since each treats NULL as distinct from every other NULL.
Best Practices
- Put the column most often filtered alone first in a composite index.
- Don't create a composite index and the single-column indexes it already serves via the leftmost-prefix rule, that's pure duplicated write overhead.
- Use CREATE UNIQUE INDEX deliberately when uniqueness should span multiple columns together, not just one column at a time.
Interview Questions
What is the leftmost-prefix rule, and why does it matter when designing a composite index?
A composite B-tree index is physically sorted by its leftmost column first, then by the next column within matching groups of that one, and so on. A query can only use the index efficiently if its filter starts from that leftmost column, a filter on a later column alone can't skip ahead into the sorted structure. It matters because column order in CREATE INDEX isn't cosmetic, it decides exactly which queries the index can actually speed up.
How does SQL Server's handling of NULL in a unique index differ from the other major databases?
SQL Server treats NULL as a real, comparable value for uniqueness purposes, so it only allows one row with a given combination of NULLs in a unique index or constraint. MySQL, MariaDB, PostgreSQL, SQLite, and Oracle all treat NULL as distinct from every other NULL, including another NULL, so they allow any number of rows with NULL in the same uniquely-indexed column.
Summary
A composite index covers multiple columns in a declared order, and is only usable starting from its leftmost column, the same leftmost-prefix rule on every database covered here, since it comes from how a B-tree is physically built, not from any one engine's choice. A UNIQUE index enforces uniqueness directly, nearly identical to a UNIQUE constraint's underlying mechanism, with SQL Server as the one database treating NULL as a comparable value rather than as always-distinct.
What's Next?
Knowing an index exists isn't the same as knowing a query actually used it. The next lesson covers EXPLAIN and execution plans, how to see exactly how a query really ran.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →