stash
stash copied to clipboard
stashapp
Performance improvements
These are some performance improvements mainly related to SQL queries:
4103f7a5 enables a build tag for sqlite_stat4 table generation when doing an ANALYZE, which should in theory allow sqlite to make better query planning choices, especially for complex filters. I haven't seen much of an improvement but it can't really hurt. Unfortunately it won't actually affect anyone on develop until the next migration as they would have already run ANALYZE.
423bf436 replaces the complicated joins used in studio filter criterion queries with simple where clauses. This can be done without affecting functionality because there is no join table for studios (one-to-many relationship).
I have ~2M images all in galleries, where only the galleries and not the images themselves have been tagged by studio. After #2904, queries for total studio image count were taking over 2 seconds making the studio page very slow to load. EXPLAIN QUERY PLAN showed that sqlite was deciding not to use the index and just scanning the rows sequentially. With these simpler queries, it does use the index and so they are near-instant.
~~315383b2 adds an index on performer stash ids, as queries for a performer's stash id (ie when loading the performer page) were taking ~800ms for me with ~4000 performers (99% of which have stash ids). I don't know of a better place to put it than in the schema 32 migration file, so it also won't affect anyone who's on develop unless they manually add the index.~~
ec79383a stops lists from loading data before saved filters have been loaded. This comes from the images tab whose default sort is by path, which is comparatively very slow especially after files-refactor. Before, even if you have a default saved filter with a different sorting as I do, the list would unnecessarily load the default path filter in the background before loading and displaying the actual saved filter. For me, a sort by path takes ~2.5s, modtime and filesize ~2s, image count ~1.2s, and random 900ms, with all the other types taking between 200-600ms. For all of these, sqlite is using indexes on each included table, so there's no easy way to improve the times. My scenario is in the minority so it's probably not reason enough to change the default sort, but it could be considered.
I've added the index from this PR in #2933 so it can be removed here. We wouldn't be adding it to the existing migration in any case because then users already on schema 32 wouldn't get it.
