On Friday, 1 November, 2019 14:52, Thomas Kurz <[hidden email]> 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.
sqlite> create table x(a,b);
sqlite> create table y(b,c);
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)
>----- Original Message -----
>From: Keith Medcalf <[hidden email]>
>To: SQLite mailing list <[hidden email]>
>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.