Introduction
Automatically-created indexes
SQL index best practices
Instruction
Okay. Even though we shouldn't create too many indexes for performance reasons, we typically consider date columns as good candidates for indexes. In many real-life situations, date columns are frequently used to filter rows. Take a look:
CREATE TABLE passenger_count ( id integer PRIMARY KEY, line varchar(3), day date, count integer );
The table above contains the daily number of passengers travelling on a given subway line. The day column is a good candidate for an index – we'll probably want to filter rows based on time ranges quite often. Let's create an index on that column:
CREATE INDEX count_day_index ON passenger_count(day);
Exercise
You're given the following definition of the invoice table:
CREATE TABLE invoice ( id integer PRIMARY KEY, number varchar(18) UNIQUE, issue_date date, customer_id integer, amount decimal(10, 2), currency char(3) );
Create an index named issue_date_index on the issue_date column.
