Indexes

[00:00:00]
>> Brian Holt: If you're Spotify and you're scanning a hundred million rows every time that you land on the homepage, you have a problem, you should use an index. So let's figure out how to do that. So let's do a, first of all, pragma index underscore list, track.
>> Brian Holt: This is gonna tell you all of the indexes that already exist on track.

[00:00:33]
As you probably would have guessed, they're the foreign keys. That's just how foreign keys work. They are gonna happen in index. Because whenever you insert into another table or delete from another table, it's got to query the other table and they want all of those to be really, really fast.

[00:00:48]
So they just automatically index foreign keys for you. Notice that these are just the ID's. We do not have an ID for the name, which let's suppose that that's what we're gonna be querying a lot, and we have a lot of them. So what we're gonna do is we're gonna say, create index IDX.

[00:01:12]
Everyone calls them IDX, I don't know why idx is index, right? But track name. On track name, okay? This is going to create an index on the name column on the track table. So now, if we look at our indexes, notice we got IDX track name here. Cool, like that.

[00:01:39]
>> Brian Holt: Let's run our explain query plan, Black Dog here again. Remember up there, it says scan track. We're hoping that it says search track. Yeah, using index IDX, track name. Why is this really cool? This means that it's using its index to find those tracks as opposed to using that scan of like looking at every item in the list.

[00:02:08]
And what the way it does is it uses a B-tree. I'm definitely not gonna explain to you too much about what a B-tree is. Stands for balanced tree. There is a phenomenal friend of master's course. Just kidding, I mean, I like it. Completing show of computer science, there is one down here on trees.

[00:02:35]
And pretty, this AVL tree is kind of like the junior version of what it's doing inside of a B-tree. This is telling how to self balance a tree. So, that's what it's doing underneath the hood for you. So instead of having to look through 3500 rows, it's gonna have to look at log n of those, right?

[00:03:00]
Which is gonna be like. My math isn't great here, but under ten, right? It's not gonna have to look at very many of them. I wish that SQLite would tell you how much computation it took. Postgres does an awesome job of saying, I took this long and I could have taken this long, but SQLite just doesn't do that for you.

[00:03:25]
Again, normally cuz it doesn't need to, it's usually fast enough. But search and scan, those are the two things that you're looking for when it comes to query plans in Europe. Again, you don't need to index everything. In fact, when you index something, it makes all of your searches that can use it, all of your selects that can use it faster.

[00:03:49]
It makes all of your inserts slower because now every time you insert, you have to go update your B-tree. It makes your deletes slower because you have to go update the B-tree. All those things get slower because now you have to update both an index and you have to update the table.

[00:04:07]
So if you have a high right table, you want to be really cautious about how many indexes you use on it.
>> Brian Holt: Cool, I'm not going to get too much into it more than that. SQLite really only has that one index that it makes available to as opposed to Postgres where you have several types of indexes to choose.

[00:04:30]
But yeah, use them sparingly. Use them on high hot path traffic. And usually I don't try and guess, usually I just kinda wait until they become a Problem and then I index them, but it's up to you. Sometimes it's just obvious, right?