It's not gonna account for 88 minutes, but out of curiosity is there a way to separately report the "delete and commit" time from the "dang this WAL needs to be checkpointed now" time?
-----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of Thomas Kurz Sent: Thursday, October 31, 2019 11:10 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] DELETE extremely slow > Something is wrong. If you did multiple commands like > > DELETE FROM MyTable; > > to your child tables, they should be fast. Have you run an integrity check ? I created a new database now, added the missing index "trace(datasetid)" as suggested by Keith. The result of "DELETE FROM dataset" is now 88 minutes, which of course is better than before where it took hours, but not nearly as quick as I'd expect... @Warren: > Is that command representative of actual use, or are you deleting all rows > just for the purpose of benchmarking? Usually I want to delete only several datasets, but not all. I left out the where-clause for simplification now. @Keith: > and in the face of enforced foreign key constraints will always delete the > rows one by each even if dependent (child) tables have no rows. Yes, but I'd expect that MariaDB has to do the same, but takes clearly less than 1 minute instead of 88 minutes... :confused: _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
