You have no index on trace(datasetid) ... You have no index on metadata(datasetid) though the compound indexes in which datasetid is the first element *should* be sufficent.
.eqp on or .eqp full before issuing the delete command will tell you what the plan is. -- 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 04:25 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: Re: [sqlite] DELETE extremely slow > >Yes, please apologize, I indeed forgot to attach the table definitions: > >CREATE TABLE dataset ( > id INTEGER PRIMARY KEY AUTOINCREMENT > UNIQUE > NOT NULL, > name STRING DEFAULT NULL > COLLATE NOCASE, > is_latest BOOLEAN NOT NULL > DEFAULT 1, > created_at DATETIME DEFAULT CURRENT_TIMESTAMP >); > >CREATE TABLE trace ( > id INTEGER PRIMARY KEY AUTOINCREMENT > UNIQUE > NOT NULL, > name STRING DEFAULT NULL > COLLATE NOCASE, > datasetid INTEGER REFERENCES dataset (id) > NOT NULL, > quantityid INTEGER REFERENCES quantity (id) > NOT NULL, > stored DATETIME DEFAULT NULL, > created_at DATETIME NOT NULL > DEFAULT CURRENT_TIMESTAMP >); > >CREATE INDEX trace_idx_01 ON trace ( > quantityid >); > >CREATE INDEX trace_idx_01 ON trace ( > quantityid >); > >CREATE TABLE item ( > id INTEGER PRIMARY KEY AUTOINCREMENT > UNIQUE > NOT NULL, > traceid INTEGER REFERENCES trace (id) > NOT NULL, > freq BIGINT NOT NULL, > value REAL NOT NULL, > noiseflag BOOLEAN DEFAULT NULL >); > >CREATE INDEX item_idx_01 ON item ( > traceid >); > >CREATE TABLE metadata ( > id INTEGER PRIMARY KEY AUTOINCREMENT > UNIQUE > NOT NULL, > parameter STRING NOT NULL > COLLATE NOCASE, > value STRING NOT NULL > COLLATE NOCASE, > datasetid INTEGER DEFAULT NULL > REFERENCES dataset (id), > traceid INTEGER DEFAULT NULL > REFERENCES trace (id), > itemid INTEGER DEFAULT NULL > REFERENCES item (id) >); > >CREATE INDEX metadata_idx_01 ON metadata ( > parameter, > value, > datasetid, > traceid, > itemid >); > >CREATE INDEX metadata_idx_02 ON metadata ( > datasetid, > traceid >); > >CREATE INDEX metadata_idx_03 ON metadata ( > traceid >); > >CREATE INDEX metadata_idx_04 ON metadata ( > datasetid, > itemid >); > >CREATE INDEX metadata_idx_05 ON metadata ( > traceid, > itemid >); > >CREATE INDEX metadata_idx_06 ON metadata ( > itemid >); > >CREATE INDEX metadata_idx_07 ON metadata ( > datasetid, > parameter >); > >CREATE INDEX metadata_idx_08 ON metadata ( > traceid, > parameter >); > >CREATE INDEX metadata_idx_09 ON metadata ( > parameter, > traceid >); > >CREATE INDEX metadata_idx_10 ON metadata ( > parameter, > datasetid, > traceid, > itemid >); > >CREATE TABLE quantity ( > id INTEGER PRIMARY KEY AUTOINCREMENT > UNIQUE > NOT NULL, > name STRING NOT NULL, > unit STRING NOT NULL, > sumrule [SMALLINT UNSIGNED] DEFAULT NULL, > created_at DATETIME DEFAULT CURRENT_TIMESTAMP, > UNIQUE ( > name, > unit, > sumrule > ) > ON CONFLICT IGNORE >); > > > > > >----- Original Message ----- >From: Dominique Devienne <ddevie...@gmail.com> >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Sent: Thursday, October 31, 2019, 11:06:07 >Subject: [sqlite] DELETE extremely slow > >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 > >_______________________________________________ >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
