I had seen this before, but I can't recommend it enough.
I very much like resources like this that explain things by explaining how they are implemented. Once you understand the basics of how a database is implemented, a lot of the performance implications of your queries make sense.
It also really helps in the understanding of what is going on in EXPLAIN output.
I agree. I took an Oracle course on this stuff a few years ago. This has proven invaluable for me in understanding performance in a variety of databases since.
SQLite only makes B-Tree indices, not hash or bitmap, and there are no clustered indices, for instance. But there are indices spanning multiple columns, there are ephemeral indices (when doing an n^2 query would be prohibitive), there are self-analytics (to gauge the efficacy of using an index in a query).
It's an entire book, so it's tough to say in a small comment box.
If you're doing queries across a 5MB database on a phone, you should be fine with or without optimizations.
If you're doing queries across a 5MB database frequently on a phone, you should think carefully about your indexes because you're responsible for battery life.
I would just like to point out that this "ebook" is not really free because it is licensed under CC BY-NC-ND; meaning you cannot make derivative works from it. Eg: If you take the contents of the website and turn it into an epub manually you cannot legally distribute said epub.
In fact, the only way you could distribute the contents is by having an identical mirror to the site.
I very much like resources like this that explain things by explaining how they are implemented. Once you understand the basics of how a database is implemented, a lot of the performance implications of your queries make sense.
It also really helps in the understanding of what is going on in EXPLAIN output.