Let's continue to talk about the proper care and feeding of your SQLite database such that it can reach its full potential. We just talked about vacuuming the database. Now we're gonna talk about optimizing and analyzing it. So SQLite needs to know, some statistics about the tables and the indexes, all of that kind of stuff so that it can make good decisions when it comes time to optimize a query. There are some commands we can run to populate those statistics and frankly we should be running those relatively often, especially after the table has changed in a dramatic way either by a schema change, inserting, updating, or deleting a bunch of data.

So we're gonna look at optimize and analyze in this video. Let me show you how. We're back in this vacuum dot SQLite table and I wanna show you first selecting from this SQLite stat 1 table. This is where SQLite keeps a lot of its stats. And so if you see this table hanging around, don't delete it.

Don't don't touch it. So you can see that here is the table and the index and some stats about that index. And you'll notice it doesn't show up in the dot tables. It is kind of it. It's an internal table so you shouldn't really see it, but if you have a tool that shows it and you delete it, well you've messed up.

Don't do that. Okay. So how did that, how did that row get there? Well, I ran analyze on that table. So let's go ahead and do something a little bit different.

There is a command called pragma optimize. And if you run this, if you run pragma optimize, it could potentially analyze the tables and the indexes. This is a great this is a great, command to run because it will only analyze tables if necessary. So you can see if you pass in, I think it's this code, if you pass that in it's going to tell you what commands it will run. So passing this in is kind of like the dry run flag.

You say, hey listen, I'm about to run optimize but I kinda wanna know I kinda wanna know what you're gonna get up to. You can pass that flag in and it will show you this is what I'm going to get up to. So then if you turn around and run pragma optimize and then we were to select from that SQL stat table again, you see we got some more stuff in here. We've got, it looks like the users table was analyzed which is what it told us it was going to do. And the indexes that it inspected were email is pro and just email.

So this optimize command can potentially run many analyze commands. You can also run analyze all by yourself. If you just were to do that, you could just run analyze and that's it. So nothing nothing has changed here, but you can run that on your own. I like the fact that Optimize keeps track of what needs be analyzed and what doesn't and will run it for you.

Now, when do we want to run Optimize? Pretty often. Pretty often because it could be a no op which is great but it could do some work that's really important and make your queries a lot faster. Now this can get expensive on an extremely large database and so there's a way to trim that down. You can say pragma analysis limit and you can set that.

Let's just read it first. So right now it's set to 0 which means totally unlimited. Do anything that you want. You can set this to any value between 1 and a 1000. Zero is unlimited.

So 1 and a 1000. The docs recommend 400 as a good, intermediate spot. So that's where I'm gonna land as well. And then if you were to run pragma optimize again, in this case, if it were to run any analysis, it would be running a partial analysis and not a a full analysis. And listen, a partial analysis is better than no analysis at all and so this is a good starting place and I would potentially recommend running this on a schedule.

Maybe running this every couple of hours. You the docs do recommend that you can run this upon closing every connection. That feels a little bit aggressive to me especially especially in, potentially a web app world where you're having lots of connections being opened and closed all the time. I don't think running this on closed is a very good idea. The same reason I don't think running vacuum or rather auto vacuum is a very good idea because we're just we're just kinda thrashing this thing with connections in the web app world instead of having, you know, one long lived connection maybe in the mobile world.

And so, I would set this up on a timer and run this maybe every hour or every couple of hours. Or you could of course run this, automatically after a big update to the table whether schema or data. But regardless, I do recommend running pragma optimize when you do it. It's kind of up to you. The analysis limit that you choose is kind of up to you.

But definitely get this set up and running on your SQLite database.