Composite index: an index on several columns in SQL

Problem

The last ten

The “Recently viewed” page shows the customer’s last 10 visits, starting from the newest. The index on customer_id has already been created. Look at the plan:

SQLdatabase: shop

EXPLAIN QUERY PLAN
SELECT seen_at, product_id FROM visits
WHERE customer_id = 1234
ORDER BY seen_at DESC LIMIT 10;

The index finds the 60 visits, but does not give them sorted by date. The database sorts them itself (USE TEMP B-TREE). For a customer with thousands of visits this is the slowest part, and only 10 of them are needed.

The idea

Two columns in one index

An index can cover several columns: CREATE INDEX name ON table(column1, column2). This is a composite index. It is sorted like a phone book: first by surname, and for the same surname by first name.

The index on (customer_id, seen_at) is sorted by customer first, and the visits of one customer are sorted by date. The database finds the customer and reads their dates from the end backwards. No sorting is needed:

SQLdatabase: shop

EXPLAIN QUERY PLAN
SELECT seen_at, product_id FROM visits
WHERE customer_id = 1234
ORDER BY seen_at DESC LIMIT 10;

The same index also helps when the condition is on both columns. The database goes to the customer and searches their part from the date onwards:

SQLdatabase: shop

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM visits
WHERE customer_id = 1234 AND seen_at >= '2026-01-01';

And on the first column alone, as if it were an index on customer_id only:

SQLdatabase: shop

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

Try it

Visits over a period

Customer service often asks how many times a customer has looked at products from a given date onwards. Create an index on just these two columns that makes the query a SEARCH on both the customer and the date.

SQLdatabase: shop

SELECT COUNT(*) FROM visits
WHERE customer_id = 1234 AND seen_at >= '2026-01-01';

Solution

CREATE INDEX idx_visits_customer_seen ON visits(customer_id, seen_at);

See it

Left to right

With the same index, the per-day report asks only about the date:

SQLdatabase: shop

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM visits WHERE seen_at = '2026-03-01';

The plan is SCAN. It says USING COVERING INDEX, because COUNT(*) is computed from the index, but the database still reads all 300,000 entries in it. Run the query itself and look at the time:

SQLdatabase: shop

SELECT COUNT(*) FROM visits WHERE seen_at = '2026-03-01';

In a phone book you cannot find everyone called “Maria” without reading the whole book. First names are sorted only within each surname.

This is the left-to-right rule: an index on (a, b) helps with conditions on a, on a and b, and with ORDER BY b when a is given with =. For a condition on b alone it does not help. If you are not sure, check with EXPLAIN QUERY PLAN.

The idea

What an index costs

Indexes are not free. Every new row has to be written into its place in every index of the table. The two fields below each add 100,000 visits. The first one has no indexes, the second has four. Run them and compare the times:

SQLdatabase: shop

INSERT INTO visits (customer_id, product_id, seen_at)
SELECT customer_id, product_id, seen_at FROM visits WHERE id <= 100000;
SELECT COUNT(*) FROM visits;

SQLdatabase: shop

INSERT INTO visits (customer_id, product_id, seen_at)
SELECT customer_id, product_id, seen_at FROM visits WHERE id <= 100000;
SELECT COUNT(*) FROM visits;

The same goes for an UPDATE of an indexed column and for DELETE.

Indexes also take space. SQLite stores the database in pages of 4,096 bytes, and pragma_page_count() tells how many pages there are. Here is the database without indexes and with the four:

SQLdatabase: shop

SELECT page_count FROM pragma_page_count();

SQLdatabase: shop

SELECT page_count FROM pragma_page_count();

With the four indexes the database is more than three times bigger. That is why you add indexes for the queries you actually have, not for every column “just in case”.

More precisely, if you are curious

The second of the four indexes, (customer_id, seen_at), makes the first one, (customer_id), redundant: by the left-to-right rule it does that job too. Pairs like this are common and rarely noticed. PostgreSQL keeps a count of how many times each index has been used (pg_stat_user_indexes), so the unused ones can be found.

Exercise

Two tasks

1. The database has an index on (customer_id) and an index on (customer_id, seen_at). Remove the redundant one.

Remove the index whose job the other one does:

SQLdatabase: shop

SELECT name FROM sqlite_master WHERE type = 'index';

Solution

DROP INDEX idx_visits_customer;

2. The index on (customer_id, seen_at) has been created. Show the dates of customer 1234’s last 3 visits, starting from the newest.

The last 3 visit dates of customer 1234:

SQLdatabase: shop

SELECT seen_at FROM visits
WHERE customer_id = 1234;

Solution

SELECT seen_at FROM visits
WHERE customer_id = 1234
ORDER BY seen_at DESC LIMIT 3;

Test

Check yourself

1. There is an index on (customer_id, seen_at). Which condition can NOT use it for a search?WHERE seen_at = '2026-03-01'WHERE customer_id = 7 AND seen_at >= '2026-01-01'WHERE customer_id = 7

2. There is an index on (customer_id, seen_at). Do you need a separate index on customer_id alone?Yes, otherwise searching by customer is slowNo: a search by the first column uses the index on bothOnly if the table is big

3. Why do indexes slow down INSERT?They do notBecause every new row is written into its place in every index of the tableBecause the database checks their syntax

4. When is an index on a column a good idea?When queries often search, join or sort by itOnly for the primary keyAlways, for every column

Indexes over several columns

Cookies