Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DELETE extremely slow (.expert command)

[Keith Medcalf]

On Friday, 1 November, 2019 14:52, Thomas Kurz  wrote:

>That's it!!! You're a genius! Thank you very very much!

>Run Time: real 8.290 user 3.25 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_0062 ON y(b);

SCAN TABLE x (~1048576 rows)
SEARCH TABLE y USING INDEX y_idx_0062 (b=?) (~10 rows)

sqlite>

>- Original Message -
>From: Keith Medcalf 
>To: SQLite mailing list 
>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