Storing the chunk size worked, and it brought the latency down from 9s to 839ms, a 10x performance boost.
But 839ms to calculate the size of a single file was still pretty slow...
I still didn't love the idea of storing the redundant file size, and I considered alternatively creating a virtual SQLite table.
But we'd still have to populate the virtual table still at app load time, which would take ~10s per GB of data
https://www.sqlite.org/vtab.html
Hope you enjoyed following along! Here's the full PR with the fix: https://github.com/mtlynch/picoshare/pull/221
Creating the index is a one-line fix, and it doesn't have the drawback my original PR did of storing a redundant copy of the file size. https://github.com/mtlynch/picoshare/pull/230/files
I tried running the SQL on its own, and it took 743 ms