High Performance SQLite helped level up our local-first React Native apps, taking full advantage of the database features and squeezing out the best performance.Dwight Watson
Build with and integrate SQLite into your production applications.
I've made my test data available for you to use and follow along.
Everything we just learned about clustered primary and secondary indexes is correct, but it's correct as it relates to row ID tables. That secret row ID is the clustered index, but what happens if you declare it as a without row id table? Then my friends, you are into traditional MySQL and Postgres world where your primary key becomes the clustered index. So a without row ID table gives you a little bit more flexibility and with flexibility comes responsibility. I don't know if that's right.
But with that flexibility you do need to be a little bit more thoughtful about the primary key that you are choosing. I mentioned briefly in the last video that if you use a random uuid in MySQL or Postgres you're constantly gonna be breaking apart the tree and rebalancing it as you're putting as you're putting new rows and new data in there. That is true in the same way without row ID table. If you use a without row ID and you choose a random primary key, you could have some insert penalties if you use, if you have to constantly break and rebalance that tree. You could also get away with not having to do that second lookup.
Right? So, we're gonna look here in a minute at a key value store table. We're gonna declare it without row ID and normally a row ID table and we're gonna try to get a sense of if there is a difference. There is a difference but we need to find out if that difference matters. And that difference is let's take this key value let's take this key value table as an example.
We'll declare the key as the primary key and that means on disk, that is how the data is going to be arranged. So what is worse, the insert penalty of potentially rebreaking and balancing the tree or the read benefit of not having to do a second lookup after you look up the primary key to go get the row ID? We're gonna find out.