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:
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.