It will help if that specific query is running slowly. Temporarily increasing the log verbosity to ‘trace’ can cast light on which queries are running slow (the log volume produced is extreme!) and you can check the xplan for them to confirm that the same logic is applicable.
SQLITE claims to be able to handle ludicrously enormous databases, up to 140 terabytes; (nearing the sheer size that people start to be seduced by glitzy “big data” marketing from Snowflake, Hadoop, etc) however, it is curiously nonspecific about the maximum size of a table; that is given in rows, not bytes, with the note:
The theoretical maximum number of rows in a table is 264 […] This limit is unreachable since the maximum database size of 140 terabytes will be reached first.
One thought. When was this database last vacuumed and analyzed? Vacuuming is a form of ‘garbage collection’ and on SQLITE it’s a major feature of the architecture.
(Unlike some other database that are full client/server architecture with a persistent ‘kernel’ there’s no inherent way for vacuuming and analysis it to be done automatically on SQLITE so it has to be periodically manually done. Duplicati has an option to vacuum after backup, but not analyze).
Vacuuming rebuilds the datafile without ‘blank space’ from deleted rows, and in sequential order. Both of these will speed things up, possibly quite a lot, and might shrink the database, also possibly quite a lot! Analyzing gathers stats about the database tables that allow the planner to make good choices about how to execute queries and may or may not speed things up too if SQLITE is making bad execution plans
I don’t know what the consequences of vacuuming a corrupt databse could be so probably better to wait for a “good sign” from the integrity check first. Also both vacuum (potentially very, very slow) and analyze, like all SQLITE writer operations causes a full lock so you won’t be able to back up or restore etc during this time.
Examples queries to check various storage attributes that give clues as to if vaccuming is likely to be useful.