Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Single-column indexes
Multi-column indexes
6. Multi-column indexes – explanation
Additional options when creating indexes
Summary

Instruction

Good job! Now, a word of explanation about the following index:

CREATE INDEX player_multi_country_points_index
ON player (country, current_points);

When this index is created, all rows in the index are sorted first by country and then by current_points. This means we can use the following queries with the index:

SELECT ...
FROM player
WHERE country = 'Russia';

Or:

SELECT ...
FROM player
WHERE country = 'Russia'
  AND current_points > 10;

However, the index won't be used with this query:

SELECT ...
FROM player
WHERE current_points > 10;

In the example above, we didn't use the first column from the index in the WHERE clause. The rows are first sorted by the country, so there's no quick way to retrieve players with more than 10 points from each country.

The image below shows how and when the index is used in the above queries:

LEAD

That's why you should always think about the order of columns in an index. Start with the column you'll be most likely to use when searching for values or ordering rows.

Exercise

Click Next exercise to continue.