Database Indexes¶

Resources¶

Faster reads and queries
slow queries are a common reason why websites are slow
- slow websites -> less profit

like tabs of a nice dictionary

links to the page, has no other info other than the first letter

Index: data structure

keys aren't necessarily indexed
- example: all the column fields form a key
- key: list of columns that uniquely identify any row

Primary key is always indexed

b-tree!

It will only have the data in the index

Downside to database indices

Work to maintain the index 2. reads are slower 3. or you have to defrag/reindex
CREATE INDEX is expensive
1. especially on a large table
Extra space 4. b trees and hash tables 5. some indexes can be huge, especially if the table is huge

Student(year, major)

sort by birthday

useful if your queries are like

SELECT *
FROM Student s
WHERE s.year = 2021 AND major = 'Computer Science'

Not useful if you have a query like

Data not sorted wrt secondary index

so it has to do a sequential scan

SELECT *
FROM Student s
WHERE major = 'Computer Science'

Problem with this query

Table is indexed on created_at

SELECT SUM(total)
FROM orders
WHERE YEAR(created_at) = 2013;

can't use created_at index because it's only indexed on the raw dates

create an index on YEAR(created_at)

CREATE INDEX total_created_at_index ON orders(created_at, total;

-- ...
WHERE created_at BETWEEN January 1, 2013 AND December 31, 2013

Cluster index????

Last update: 2023-04-24