Great! Let's get started. We're going to work with a table that keeps track of all the players in an online RPG game. The table looks like this:
CREATE TABLE player ( id integer PRIMARY KEY, first_name varchar(64), last_name varchar(64), year_born integer, country varchar(64), current_points integer );
The current_points column indicates how many points a given player has acquired since the beginning of the season. We're frequently going to filter and order rows using this column, so we'd like to create an index on it. We'll use the following code:
CREATE INDEX points_index ON player (current_points);
The above code means that we want to create an index named points_index on the column current_points in the player table. It's an INTEGER column, which means the index will boost performance when we use current_points in a WHERE clause with operators like =, >, or <=. By default, most databases will create a B-tree index when we run a CREATE INDEX statement.
A taxi start-up has become extremely successful. They need your help to optimize their database's driver table, which has grown a lot! Take a look at its definition:
CREATE TABLE driver ( id integer PRIMARY KEY, full_name varchar(128), year_born integer, city varchar(64), rating decimal(3, 2), ride_count integer );
The company frequently needs to filter for drivers who have completed a certain number of rides. Create an index (named rides_index) on the column ride_count.
