Here's a preview from my zine, Become a SELECT Star!! If you want to see more comics like this, sign up for my saturday comics newsletter or browse more comics!
get the zine!
read the transcript!
By default, if you run SELECT * FROM cats WHERE name = 'mr darcy' the database needs to look at every single row to find matches.
database, sad: reading 30 GB of data from disk takes like 60 seconds by itself, you know!
(at 500 MB/s SSD speed)
Indexes are a tree structure that makes it faster to find rows. Here’s what an index on the ’name’ column might look like.
a-z
aaron to ahmed
aaron to abdullah
agnes to ahmed
molly to nasir
60 children
waseem to zahra
database indexes are b-trees and the nodes have lots of children (like 60) instead of just 2.
log 60 (1,000,000,000) = 5.06
This means that if you have 1 billion names to look through, you’ll only need to look at maybe nodes in the index to find the name you’re looking for (5 is a lot less than 1 billion!!!).
person 1: are you saying indexes can make my queries 1,000,000x faster?
person 2: yes! actually some queries. on large tables are basically impossible (or would take weeks) without using an index!