Optimization

From fruit and state to millions of titles

Gregory S. DeLozier, PhD

The Small Table

create table fruitsforsale (
    fruit text, state text, price real
);

Seven rows. Oranges from Florida and California.

Follow the rows through the SQLite illustrations.

A Full Table Scan

select price from fruitsforsale
where fruit = 'Peach';

Time grows with N.

Lookup by Rowid

select price from fruitsforsale
where rowid = 4;

Time grows with log N.

An Index

create index Idx1 on fruitsforsale(fruit);

A sorted copy of a column, plus rowids.

Using an Index

Two binary searches: the index, then the table.

Reading a Query Plan

explain query plan
select price from fruitsforsale
where fruit = 'Peach'

Look for SCAN first.

Two Conditions

select price from fruitsforsale
where fruit = 'Orange' and state = 'CA';

Find the oranges. Check their states.

A Multi-Column Index

create index Idx3 on fruitsforsale(fruit, state);

Fruit first; state breaks ties.

A Covering Index

create index Idx4 on fruitsforsale(fruit, state, price);

The index contains the answer: price.

OR

select price from fruitsforsale
where fruit = 'Orange' or state = 'CA';

Combine matches; return each matching row once.

Sorting

Collect every row, then sort: K log K, plus temporary storage.

Sorting with an Index

An index is already in order.

Search and Sort Together

where fruit = 'Orange' order by state, with an index on (fruit, state).

Partial Sort

order by fruit, price: the index gives fruit order; SQLite sorts within each fruit.

Statistics

analyze;

SQLite picks among indexes by estimated selectivity.

Without statistics, the choice is close to arbitrary.

It also makes a skip-scan possible.

Real Data

Gzip-compressed tab-separated values (TSV). Missing value: \N.

Personal and non-commercial use only.

Keep the Data Out of Git

Before downloading, add to the root .gitignore:

**/data/
**/imdb.db
**/imdb.db-*

Commit the code and .gitignore. Keep the data local.

Check: git check-ignore -v data/title.basics.tsv.gz

Get Your Own Titles Database

IMDb supplies two compressed data files.

The course supplies import_imdb.py and schema.sql.

1. Download title.basics.tsv.gz and title.ratings.tsv.gz into data/.
2. Run python3 import_imdb.py in the code folder.
3. Open the resulting imdb.db.

The loader reads local files; it does not download them.

Loading in Chunks

12.8 million rows in 54 seconds. No secondary indexes yet.

One Index, One Query

select tconst, start_year from titles
where primary_title = 'The Godfather'

47 titles are called “The Godfather”.

What an Index Costs

500,000 inserts: 0.25 s with no index, 0.37 s with one, 1.06 s with three.

Two Conditions on Titles

select count(*) from titles
where title_type = 'movie' and start_year = 1994;

Column Order Matters

start_year alone: full walk of the index, 432 ms.

After analyze: skip-scan, 1.9 ms.

title_type alone: search, 18 ms.

Covering a Titles Query

select primary_title ... where title_type = ? and start_year = ?

OR on Titles

select tconst from titles
where start_year = ? or primary_title = ?

LIKE

A wildcard at the front cannot use an ordinary index.

Top Ten by Votes

select tconst, num_votes from ratings
order by num_votes desc limit 10

The plan still says SCAN. The sort is gone, and limit stops the walk.

Python or SQL?

Measure.

Stored Procedures

create or replace procedure
    change_owner(pet integer, new_owner text)
language plpgsql
as $$ ... $$;

call change_owner(1, 'Sam');

One call: read, update, log.

SQLite has none. This is PostgreSQL.

Choosing Indexes

Start from the queries.

Index what you search, join, and sort by.

Equality columns first, range columns last.

Drop an index that starts another.

Read the plan, then measure.

Exercise: Tune Three Queries

Longest movies over 300 minutes.

Titles rated 9.5 or higher with 10,000 or more votes.

Average rating of movies by decade.

Read the plan. Predict the index. Check.