>_ IzzyLab

Hands-on task

This task is step 8 of the course “SQL with SQLite”

SQLite: index on a foreign key

easy~3 min

The table orders(id, customer_id, amount) in the database /root/shop.db is often filtered by the customer_id column, and such queries are slow. Create an index named idx_customer on the customer_id column.

What you'll practise:
  • sqlite3
  • create index
  • indexes
  • query optimization
  • sqlite_master

Topic breakdown

An index is a separate sorted structure (usually a B-tree) that lets you find rows by a column's value without scanning the whole table. It speeds up filtering and joins on that column at the cost of extra space and slightly slower writes, which is why indexes are placed on frequently filtered fields like foreign keys. It is created with the CREATE INDEX ... ON table(column) statement, and you can see the object in sqlite_master by type='index'.

What's checked

  1. index idx_customer created
  2. index built on the orders table
  3. index built on the customer_id column

What it looks like