On 1 Nov 2019, at 10:11pm, Keith Medcalf wrote:
> Just run ".schema" or ".schema --indent"
But there's no need, since I think the problem has been solved, right ?
It came down to lack of indexes. Which is consistent with the times and
EXPLAIN QUERY PLANs posted.
On Friday, 1 November, 2019 16:04, Thomas Kurtz wrote:
> The database schema is not a secret. If it helps, I can post it, that's
> no problem. Is it enough to run ".dump" on a database without data?
Just run ".schema" or ".schema --indent" which will output only the schema
definitions and not
The database schema is not a secret. If it helps, I can post it, that's no
problem. Is it enough to run ".dump" on a database without data?
- Original Message -
From: Keith Medcalf
To: SQLite mailing list
Sent: Friday, November 1, 2019, 22:57:02
Subject: [sqlite] DELETE extremely slow
On Friday, 1 November, 2019 15:12, Simon Slavin wrote:
>So the slow-down in the DELETE FROM command is caused by a TRIGGER, but
>there are no TRIGGERs on DELETE ? I don't understand that. Can someone
>explain, please ?
The code indicates that they are AFTER DELETE so presumably they are
So the slow-down in the DELETE FROM command is caused by a TRIGGER, but there
are no TRIGGERs on DELETE ? I don't understand that. Can someone explain,
please ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
On Thursday, 31 October, 2019 16:54, Thomas Kurz :
>I did it again, same file:
>SQLite version 3.30.1 2019-10-10 20:19:45
>Enter ".help" for usage hints.
>sqlite> pragma foreign_keys=on;
>sqlite> .timer on
>sqlite> delete from dataset;
>Run Time: real 5249.891 user 2412.812500 sys 2606.531250
Yes, there are triggers. I didn't post them because I didn't see any
correlation to the DELETE query as they are only INSERT and UPDATE triggers:
CREATE TRIGGER item_inserted AFTER INSERT ON item BEGIN UPDATE trace SET typeid
= (SELECT id FROM "type" WHERE name = 'modified') WHERE trace.id =
That's it!!! You're a genius! Thank you very very much!
Run Time: real 8.290 user 3.25 sys 1.906250
- 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
On Friday, 1 November, 2019 10:20, Simon Slavin wrote:
>On 1 Nov 2019, at 4:17pm, Simon Slavin wrote:
>WHERE (c1 IS NULL) OR (C1 != 2)
> which could quite reasonably return rows. However, the NULL possibility
> may be redundant. I can't tell without tests.
The expression NOT (c1 IS
On Friday, 1 November, 2019 09:12, And Clover wrote:
> CREATE TABLE t0 (c0);
> CREATE TABLE t1 (c1);
> INSERT INTO t0 VALUES (1);
> SELECT c0
> FROM t0 LEFT JOIN t1 ON c1=c0
> WHERE NOT (c1 IS NOT NULL AND c1=2);
>Expected result: (1)
>Actual result: no rows
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
One of your delete triggers is causing a table scan of table item. Multiple
scans of table item, for each row of dataset. From the code it looks like it
is scanning for a match on "nameid". And there is not index on item(nameid
...) ...
--
The fact that there's a Highway to Hell but only
Jim Morris, on Friday, November 1, 2019 12:07 PM, wrote...
>
> Using a sub-select should work
>
> select
>
> (select count(n) from t0) as "t0_count",
>
> (select count(n) from t1) as "t1_count",
>
> (select count(n) from t2) as "t2_count"
>
> ;
Thanks. Works.
josé
> On 11/1/2019 9:07 AM, Jose
On 01/11/2019 16:20, Simon Slavin wrote:
It is actually
WHERE (c1 IS NULL) OR (C1 != 2)
which could quite reasonably return rows.
Yes, and with this OR filter the quoted example does indeed return rows.
The version with:
WHERE NOT (c1 IS NOT NULL AND c1=2)
*should* be equivalent to
Looks like you have triggers going on there. You only gave us the table and
index definitions. What are the on delete triggers you have?
-Original Message-
From: sqlite-users On Behalf Of
Thomas Kurz
Sent: Thursday, October 31, 2019 6:54 PM
To: SQLite mailing list
Subject: Re:
On 1 Nov 2019, at 4:17pm, Simon Slavin wrote:
> This is a cut-down example, right ? You can't possibly mean to do that WHERE
> clause in production code. It amounts to
>
>WHERE (c1 IS NULL) AND (C1 != 2)
I'm so sorry. It is actually
WHERE (c1 IS NULL) OR (C1 != 2)
which could
On 1 Nov 2019, at 3:12pm, And Clover wrote:
>WHERE NOT (c1 IS NOT NULL AND c1=2);
>
> Expected result: (1)
> Actual result: no rows returned
This is a cut-down example, right ? You can't possibly mean to do that WHERE
clause in production code. It amounts to
WHERE (c1 IS NULL)
Using a sub-select should work
select
(select count(n) from t0) as "t0_count",
(select count(n) from t1) as "t1_count",
(select count(n) from t2) as "t2_count"
;
On 11/1/2019 9:07 AM, Jose Isaias Cabrera wrote:
> Jose Isaias Cabrera, on Friday, November 1, 2019 11:51 AM, wrote...
>>
>>
Jose Isaias Cabrera, on Friday, November 1, 2019 11:51 AM, wrote...
>
>
> Richard Hipp, on Friday, November 1, 2019 11:41 AM, wrote...
> >
> > On 11/1/19, Jose Isaias Cabrera, on
> > > sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1
> > > AS
> > > b LEFT JOIN t2 AS c;
>
Richard Hipp, on Friday, November 1, 2019 11:41 AM, wrote...
>
> On 11/1/19, Jose Isaias Cabrera, on
> > sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 AS
> > b LEFT JOIN t2 AS c;
> > 3375|3375|3375
> >
> > Huh? I expected the result:
> >
> > 15|15|15
>
> You did a
On 11/1/19, Jose Isaias Cabrera wrote:
> sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 AS
> b LEFT JOIN t2 AS c;
> 3375|3375|3375
>
> Huh? I expected the result:
>
> 15|15|15
You did a three-way join on tables with 15 rows each. 15x15x15 is 3375.
A LEFT JOIN
Greetings.
Please take a look at the following:
create table t0 (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t0 (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4,
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4,
'2019-02-11');
insert
Hi,
CREATE TABLE t0 (c0);
CREATE TABLE t1 (c1);
INSERT INTO t0 VALUES (1);
SELECT c0
FROM t0 LEFT JOIN t1 ON c1=c0
WHERE NOT (c1 IS NOT NULL AND c1=2);
Expected result: (1)
Actual result: no rows returned
This appears to be a regression in 3.30; 3.29 and earlier
On Thu, Oct 31, 2019 at 9:52 AM Keith Medcalf wrote:
> On Thursday, 31 October, 2019 07:17, Jeffrey Walton
> wrote:
> ...
> >/* negative for days in the past */
> >int days = 120;
> >days = -days;
>
> >const char DELETE_STMT[] = "DELETE from blacklist " \
> >"WHERE dtime
> Do you have memory to run this in? Have you increased the sqlite cache size
> because that looks (to me) an awful lot like I/O thrashing ...
Sorry to disappoint you, Keith and Simon, but in all cases the database file
has been located on a ramdisk. It's only about 50 MB in size, btw.
>
Hi sorry,
just forget it. I have overseen a typo.
> Am 2019-11-01 um 15:00 schrieb mailing lists :
>
> Assume the following statements
>
> CREATE VIRTUAL TABLE NamesFTS USING FTS5 (LastName);
> INSERT INTO NamesFTS (LastName) VALUES('L.');
> INSERT INTO NamesFTS (LastName) VALUES('"L."');
>
>
Assume the following statements
CREATE VIRTUAL TABLE NamesFTS USING FTS5 (LastName);
INSERT INTO NamesFTS (LastName) VALUES('L.');
INSERT INTO NamesFTS (LastName) VALUES('"L."');
SELECT rowid FROM NamesFTS;
> 1
> 2
SELECT rowid FROM NamesFTS WHERE NamesFTS MATCH '"L."';
> 1
> 2
How do I get
On 1/11/62 19:15, mailing lists wrote:
Thanks.
Is there a difference between these statements with respect results,
performance etc. or are both statements describe the same thing?
They're the same in all important respects.
Dan.
a) SELECT * FROM NamesFTS WHERE FTS MATCH
Thanks.
Is there a difference between these statements with respect results,
performance etc. or are both statements describe the same thing?
a) SELECT * FROM NamesFTS WHERE FTS MATCH 'LastName:alpha FirstNames:beta';
b) SELECT * FROM NamesFTS WHERE LastName MATCH 'alpha' AND FirstNames MATCH
On 1/11/62 03:03, mailing lists wrote:
Hi Dan,
I did not know that. What was the reason that it did not work before 3.30?
The implementation of the xBestIndex method of fts3/4, and fts5 prior to
3.30.0, only allowed a single MATCH constraint to be processed and
passed through to xFilter.
30 matches
Mail list logo
