EXPLAIN

[00:00:00]
>> Brian Holt: We actually are going to get a little bit into how to performance profile. I'm just going to teach you a little bit about explain which is useful. All right, so select star from track, where
>> Brian Holt: Name equals Black Dog, which is a.
>> Brian Holt: Let's Zepton Sol.
>> Brian Holt: Looks like we got two versions of it.

[00:00:27]
That's cool.
>> Brian Holt: Yeah, well, let's just do an explain here first. So, exact same query. The only thing I want you to do now is, I just want you to put explain on the front of it and you'll get something that looks like this.
>> Brian Holt: If you're looking at that and saying like, I don't get it, me too.

[00:00:57]
I don't really understand what this is saying. Every time I look at a SQLites output of explain, it's really hard for me to make heads or tails of what it is. And I started digging into this because I thought I was maybe I'll explain it to them. And I was getting so far into what the opcodes are and what you need to be concerned about.

[00:01:15]
And I was like, yeah, no, this is like a whole course worth of material here. And frankly, it's stuff that I don't look at, so I'm not going to really explain it too much to you. What I am going to show you is the one that I do find useful.

[00:01:31]
And they all databases have their own version of this. The one in Postgres is explain, analyze, which does not work. But the one that exists in SQLite is EXPLAIN QUERYPLAN. And the big bad word to look for is scan. Scan means I'm going to look at everything in this table to figure out if it's called Black Dog or not.

[00:02:01]
So, if you remember correctly, in track we have 3500 rows. So in order to find two tracks, I had to look at 3500 of them. I'm gonna go ahead and say that's probably okay. Because, again, SQLite is really fast. Especially in 3500 rows. Now, if you're on a million rows and it's gonna happen a lot, yeah, by all means, worry about it.

[00:02:27]
>> Brian Holt: But high volume and frequent those are kind of tip of your like, okay this is too slow. Even then, I saw this while I was doing my research. We're gonna talk about indexes and how to index a column. Indexes are like medicine, that if you don't need it, you don't need it.

[00:02:53]
And if you do need it, a little bit helps. And then, too much of it is almost always a bad thing. So, be judicious about where you index things. We looked at query plan. Something that can be helpful is, and I'll do this from time to time when I'm working in SQLite, you can do .eqp on, explain query plan Plan.

[00:03:20]
So you don't have to put this every single time. It's now just going to start telling you every single time.
>> Brian Holt: So you get a scan track right there. Kind of helpful, especially if you're writing a lot of queries here and you're trying to figure stuff out.