Been waiting for a sqlite delete operation to finish for over an hour now. It’s insanely slow😬
Any sqlite ninjas out there with any tips/tricks for speeding this up?
Discussion
Are you deleting all data in the table? Use TRUNCATE https://www.sqlite.org/lang_delete.html
Else see what SQLite tells you in its query plan: https://www.sqlite.org/eqp.html
Hard to suggest optimizations without knowing what you are doing
I’m deleting all rows where a specific column contains a specific string (removing events that spam scamming links from the relay db).
Also batching. When you delete too many rows in a single query it’s a transaction that either succeeds or fails as a whole. If you can split deletes into chunks, each chunk only has to worry about those N records.
Might be better to do it in batches but not sure how to write that query though. Do I do subqueries?
I always find it easier to do recursive queries via API calls instead in code - excluding things like tree parent/child select queries.
Often there is a batch or streaming API that can be used. Unsure for SQLITE.
Ask chatgpt. It’s depressingly good