Composite index and column order in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a composite index in a database?

A composite index is an index that includes more than one column from a table. It helps speed up queries that filter or sort by multiple columns together.

intermediate

Why does the order of columns matter in a composite index?

The order matters because the database uses the index starting from the first column. Queries filtering on the first column(s) can use the index efficiently, but if the first column is missing in the query, the index might not be used.

intermediate

Given a composite index on (A, B), which query can use the index efficiently?

A query filtering on column A alone or on both A and B can use the index efficiently. But a query filtering only on B cannot use the index effectively.

beginner

How does a composite index improve query performance?

It allows the database to quickly find rows matching multiple column conditions without scanning the whole table, reducing search time.

intermediate

What happens if you reverse the column order in a composite index?

Reversing the order changes which queries can use the index efficiently. For example, an index on (B, A) helps queries filtering on B first, but not those filtering only on A.

What is a composite index?

A A type of table

B An index on a single column

C An index on multiple columns

D A database user

A composite index includes more than one column to speed up queries involving those columns.

If you have a composite index on (A, B), which query can use it efficiently?

A WHERE C = 5

B WHERE B = 5

C WHERE B = 5 AND A = 10

D WHERE A = 5

The index can be used efficiently when filtering on the first column A or both A and B, but not on B alone.

Why does column order matter in a composite index?

A Because the database uses the index starting from the first column

B Because the last column is always ignored

C Because columns are sorted alphabetically

D Because indexes only work on one column

The database uses the index starting from the first column, so queries must filter on that column to use the index.

What is the main benefit of using a composite index?

A Faster queries on multiple columns

B More storage space

C Slower inserts

D Less data in the table

Composite indexes speed up queries that filter or sort by multiple columns.

If you have an index on (B, A), which query is less likely to use it efficiently?

A WHERE B = 10

B WHERE A = 10

C WHERE B = 10 AND A = 20

D WHERE B > 5

Queries filtering only on A cannot use an index starting with B efficiently.

Explain what a composite index is and why the order of columns matters.

Think about how the database searches using the index.

Describe how a composite index can improve query performance and give an example of when it might not be used.

Consider which columns the query filters on compared to the index order.