Ian Whitney whit0694@umn.edu @ian_whitney (Slack)

select * from phone_book where last_name = 'Whitney'

^The database has no way of knowing where these records are.

^So all it can do is pull the entire table from disk into memory and check each row.

create index phone_book_last_name on phone_book(last_name);

^An index creates a Database structure that orders the data by the indexed field

^It's now easy for the database to find all rows with a last name of Whitney.

select * from phone_book where last_name = 'Whitney' and first_name = 'Ian';

^The database has to pull all Whitneys from disk and then find the ones that match by first name.

create index phone_book_first_name on phone_book(first_name);

• Your database now has to maintain two indexes
• And none of your users ever query by just first name
  • It's always last name & first name

create index phone_book_first_last_name on phone_book(first_name, last_name);

We now have 3 indexes. Which of them get used?

select * from phone_book where last_name = 'Whitney' and first_name = 'Ian';

Uses: phone_book_first_last_name

^Nothing surprising here. The index we created for this kind of query is used by this query

select * from phone_book where first_name = 'Ian';

Uses: phone_book_first_name

^This would use our first_name index. But no one does this. So delete the index. It's just taking up space and slowing things down.

select * from phone_book where last_name = 'Whitney'

Uses: phone_book_first_last_name

^The last/first name index offers the same sort as our last name index.

^It's sorted by last name first, so it's easy for the DB to identify what rows to pull from disk.

If the column(s) in your where clause are the left-most columns in a multi-column index: 👍

create index phone_book_last_first_name on phone_book(last_name, first_name);

✅ where last_name = 'Whitney'
✅ where last_name = 'Whitney' and first_name = 'Ian'
❌ where first_name = 'Ian'

Slides:

https://z.umn.edu/indexes

Video that taught me a bunch:

https://z.umn.edu/index_talk