Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Understanding Indexes

Introduction
Automatically-created indexes
SQL index best practices
8. Indexes on date columns
Summary

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.