If you are deleting all the rows of a table, then you can simply truncate the table (SQLite will do this). However if the table you are deleting all the rows from have dependent (child) tables *and* foreign key enforcement is turned on, then the rows have to be deleted on at a time so that the foreign key constraints can be checked, although if all the child tables have no rows you should be able to just truncate the parent. SQLite does not perform that optimization 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.
-- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Thomas Kurz >Sent: Thursday, 31 October, 2019 05:10 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: Re: [sqlite] DELETE extremely slow > >> Keith found the answer: you don't have the indexes required to make >your FOREIGN KEYs run quickly. > >Thanks, I will try that. > >> If you DELETE FROM the child tables first, do you get fast or slow >times ? > >Yes, I already tried deleting from each table individually. It's slow >everywhere. > >> Thee way you're doing it involves a lot of steps as SQlite works its >way through the parent table, deletes one row from that, and cascades >through the other tables, looking for and deleting related rows from >those. > >Ok, I might have errors in my declarations, but SQLite seems to have >problems as well, because MariaDB (without any explicit index defintion!) >handles the same deletion within seconds... > >_______________________________________________ >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
