Instruction
Let's see the definition of our table again:
CREATE TABLE player ( id integer PRIMARY KEY, first_name varchar(64), last_name varchar(64), year_born integer, country varchar(64), current_points integer );
Another common operation we'll need is showing the user ranking for each country. To that end, we'll need to filter and/or sort rows by two columns: country and current_points. When running a query, most database engines can only use a single index (or none at all). Creating two indexes – one for country and one for current_points – isn't optimal. What we can do instead is create a multi-column index (aka a composite or concatenated index):
CREATE INDEX player_multi_country_points_index ON player(country, current_points);
Note that the order of columns really does matter. We'll explain that in a second.
Exercise
Let's recall our table code:
CREATE TABLE driver ( id integer PRIMARY KEY, full_name varchar(128), year_born integer, city varchar(64), rating decimal(3, 2), ride_count integer );
Another common operation is looking for drivers in a given city and then filtering for drivers with a rating in a given range. Create an index named city_rating_index with the proper columns.
Stuck? Here's a hint!
Use the columns city and rating in this order.
