Throughout this entire module, we're gonna be using explain a lot and in fact we used it in the indexing module, so you should be somewhat familiar with it, but I'm gonna show you just a few other things here. Throughout this entire module we're gonna be using explain a lot, and as we do these queries, new things are going to come up in the explain output and I will explain them as they come up, but I do wanna show you a few things first. So if you'll remember from the indexing module, you can turn the explain query plan on by doing that. You can turn it off by doing that and just do explain query plan select count star from users. And that will, that will put out the query plan, but it doesn't put out the results.

So here we're gonna do EQP on and we're just gonna run it this way. But if you're running it, you know if you're running something from your application or from some sort of GUI and you can't turn EQP on, you can just do explain query plan and that will give you the same thing. Remember that the explain is a little bit opaque. What is it? Select count star from users.

This gives you like legitimately the byte code. It gives you the virtual machine operations here and this again is documented. I haven't tried to learn how to read it yet. So far I've gotten away with explain query plan, and fingers crossed, may it continue. Let me show you just a few things, a few specific things here.

And then like I said, we will, expand on this as we go throughout the rest of this module. Just as a refresher, here is, here are all the tables that we're working with. Let's go ahead and just dump out the schema. Migrations, you can ignore. SQLite sequence is maintained by SQLite itself.

Users, we've got an ID primary key then some other stuff. Bookmarks, we've got an ID primary key. And then we've got a user ID and then parent child we can skip. Index on user's email, index on first name last name and index on user ID on the bookmarks table. So with that in place, I wanna show you a few things.

First thing I wanna show you is what do I wanna show you? I wanna show you 1 of the optimizations that the query planner will make and that is called an or by union. So I have said at least 1 time that you can only use 1 index at a time. That's kind of a lie. Thanks to SQLite, that's kind of a lie.

There are situations where 2 indexes can be used, the row gathered up, and then a union will happen on those row ids. That can happen in this case. So if we do select star from select star from users where email equals, we'll do aaron dot Francis at example dotcomorfirstnameequalsaron and last name equals Francis. So we have a we have an index on email and an index on first name comma last name. So if we run this, we will see a multi index or which is super duper cool.

Way to go SQLite. And so what happens here is it searches through the index, the email index first and then it searches through the users index, the name, index on the users table second and then it takes the row ids or in our case the id. They're an alias for each other. It takes those and it unions them and then goes back and pulls the rows out of the table. So this is 1 of the things that you will see and if you see something in the query plan that you don't recognize, I encourage you to just copy it, put it in quotes, and search SQLite quotes, then that word, and most likely the SQLite docs are gonna come up.

So that's 1 that is very cool. This is a little bit more complicated than we're used to seeing just like search users using index. We're starting to see that there's a little bit more data there. Let's do another 1. Let's do select star from users where id in, select user ID from bookmarks, group by user ID having hang on, bear with me, having count greater than, I think, maybe 18 is good.

Having count greater than 18. So what in the world is all of this? We will cover subqueries and joins. This happens to be a subquery. But what we're saying is let's look at the users and give me all the users where they have more than 18 bookmarks.

So we're saying give me the user ID from bookmarks but group by the user ID and then only, give me the ones back that have a count of more than 18. So if we run that, you'll see some more words. So we've got a list sub query 1 and so this is the sub query that is being run right here. It is scanning the bookmarks index user ID. And so this, this is showing you a little bit more.

Sometimes you'll see correlated subquery, which we really don't wanna see and we'll cover that when we get to subqueries. But this is I'm just trying to show you a few more of the keywords words that you might see in an explain plan. And this 1 ran super fast. You can imagine I'm not gonna do it right now. We'll do it when we get to subqueries.

But, there is a way to rewrite this query as an exists which will never finish. And I will explain why when we get to subqueries. So this is just a brief overview of explain in all truthfulness or in all fairness. We're gonna just be covering explain throughout this entire module. So I'm not gonna try to show you everything right here, right now.