Wanna show you another cool feature about SQLite and that is automatic query time indexes. This is different than internally created indexes. Like, when you declare a column unique, SQLite is gonna put a unique index on that. This is different. Automatic indexes are, created at run time and then destroyed.

Now, you wanna have your own indexes. This automatic indexing thing is a fail safe to protect you in certain scenarios, but it's always going to be worse than creating the correct indexes. I have created a new table here, select star from bookmarks, and we limit it down to 5. We see we've got user ID and a URL. Apparently, the application that we're creating is just a bookmarking application.

It's good enough. You can find all of this data down in the description below and you can spin it up on Terso really easily if you want to follow along. So clearing out, let's take a look at indexes. We don't have any. And tables, we've got the bookmarks and the users.

And this migrations table, select star from migrations, it's nothing. It's just creating some tables and putting stuff in a batch. It's just this is, that's what I use to actually create the database and so we can ignore that altogether. Now, what I want to show you is this, the automatic, the automatic indexing being created. So let's do I think we're already in mode box.

Let's do EQP probably is already on. So let's do select star. Actually, let's do select ID and count from users left join. Now we haven't talked about joins and sub queries and all that, we'll do that later, but I just want to construct a query that will cause SQLite to add an automatic index. So if we left join, select, count star, and user ID from bookmarks, limit, no, group by user ID.

And then out here we'll say join all of that on users dot id equals user id, and then we'll do yeah, 10 is fine. So if we run this, it's gonna take a long time. We did not alias we did not alias the really messed that up. As count and then we'll find our there we go. This is gonna take a really long time and you see we do get the ID and the count of their bookmarks.

So the query did work, but we see using automatic covering index. So what happened here is SQLite looked at this and said, I'm gonna have to scan that table multiple times because they didn't put an index on it. In fact, it would be faster for me if I went ahead and and ate the penalty of creating an index just to throw it away again. But during the query I can use that index to look up all those values instead of scanning the table multiple times. So if you ever see using automatic covering index or using automatic index, that's SQLite saying, oof, rough query.

I'm gonna do my best to make it better. I wonder I wonder if we turn expert on. I think maybe it's a no. We just say expert. And then, we've gotta run all of this again.

We'll see what it says. Create index there you go. Create index with random name on bookmarks user ID. So the automatic index that is being created by SQLite is also the same index that expert mode suggests that you create. While it is cool that they have automatic indexes, this is a sign that if you're going to run this query a lot more than once, you should create your own index on it.

This is fine if you're just doing 1 off data analysis and you're like, I'm never gonna run this query again. Great. Thanks for the help. But if you run this as a part of your application and you see automatic index, that's a red flag. I should go in and create an index myself so it doesn't have to create it every query, simply to throw it away.