On Friday, 1 November, 2019 14:52, Thomas Kurz <sqlite.2...@t-net.ruhr> wrote:
>That's it!!! You're a genius! Thank you very very much! >Run Time: real 8.290 user 3.250000 sys 1.906250 I believe that the SQLite3 CLI has a command .expert that you can use to turn on assistance to tell you if the next executed SQL statement will benefit from the creation of an index. I forgot all about this command or even when it was added (it is an experimental feature). It would/should have told you immediately that you needed those two additional indexes, I should think. eg: sqlite> create table x(a,b); sqlite> create table y(b,c); sqlite> .expert sqlite> select a, x.b, c from x,y where x.b == y.b; CREATE INDEX y_idx_00000062 ON y(b); SCAN TABLE x (~1048576 rows) SEARCH TABLE y USING INDEX y_idx_00000062 (b=?) (~10 rows) sqlite> >----- Original Message ----- >From: Keith Medcalf <kmedc...@dessus.com> >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Sent: Friday, November 1, 2019, 18:07:51 >Subject: [sqlite] DELETE extremely slow > >One of your triggers requires and index on item(nameid) and there is no >index on item(nameid). >Hence it is doing a table scan to find the rows matching this trigger. >That is why the plan has multiple "SCAN item" in it. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
