On Thu, Oct 31, 2019 at 10:57 AM Thomas Kurz <sqlite.2...@t-net.ruhr> wrote:
> I'm using a database with 5 hierarchically strcutured tables using foreign > keys. The largest table contains about 230'000 entries. My problem is that > deleting in this database is extremely slow: > > pragma foreign_keys=on; > pragma journal_mode=wal; > .timer on > delete from dataset; > --> Run Time: real 197993.218 user 53015.593750 sys 54056.546875 > > I experimentally imported the same data into a MariaDB database and tried > the same operation there (without paying attention to creating any indexes, > etc.). It takes only a few seconds there. > > Is there something I can check or do to improve deletion speed? > You're not describing the schema enough IMHO. Is dataset the "top-most" table, containing the "parent" rows all other tables references (directly or indirectly), with all FKs having ON DELETE CASCADE? If that's the case, without some kind of optimization in SQLite, when the first parent row is deleted, it triggers a cascade of deletes in "child" tables, looking for rows using the parent row. So if your FKs are not indexed for those column(s), that's a full table scan each time... That's "depth first". By analyzing the graph of FKs and their ON DELETE CASCADE state, and in the specific case of fully deleting the "main parent table", SQLite could decide switch to a smarter "breadth first" delete, but I suspect it's not a compelling enough use-case for Richard to invest time on this. Try indexing your FKs, and see what happens. --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
