Re: DELETE extremely slow (.expert command)

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: DELETE extremely slow (.expert command)

Keith Medcalf

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.

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 <[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.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: DELETE extremely slow (.expert command)

Thomas Kurz
> It would/should have told you immediately that you needed those two additional indexes, I should think.

Unfortunately not. Someone told me about ".expert" some time ago and it's indeed helpful for me because I never know what indexes to create and why. But for this database everything seemed ok:

SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> pragma foreign_keys=on;
sqlite> pragma foreign_keys;
1
sqlite> .expert
sqlite> delete from dataset;
(no new indexes)

(null)
sqlite>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users