t it.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Marco Bambini
>Sent: Saturday, 29 February, 2020 01:38
>To: SQLite mailing list
>Su
On 29 Feb 2020, at 8:37am, Marco Bambini wrote:
> ORDER BY (prop_tag='ios') LIMIT 1;
>
> I would like to prioritise results based on the fact that the prop_tag column
> is 'ios'.
SQLite has a conditional construction:
CASE prop_tag WHEN 'ios' THEN 0 ELSE 1 END
So do
SELECT …
ORDER BY CA
Hi all,
Is there a way to specify an ORDER BY clause by column value?
I have a table declared as:
CREATE TABLE Table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id INTEGER,
prop_key TEXT, prop_value TEXT, prop_tag TEXT DEFAULT '*', UNIQUE(obj_id,
prop_key, prop_tag))
and a sample query:
SELECT
On Sun, Oct 20, 2019 at 8:23 PM Petr Jakeš wrote:
>
>
> On Sun, Oct 20, 2019 at 4:36 PM Keith Medcalf wrote:
>
>>
>> On Sunday, 20 October, 2019 06:58, Petr Jakeš
>> wrote:
>>
>> >On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf
>> wrote:
>>
>> >> On Saturday, 19 October, 2019 18:26, Petr Jakeš <
On Sun, Oct 20, 2019 at 4:36 PM Keith Medcalf wrote:
>
> On Sunday, 20 October, 2019 06:58, Petr Jakeš
> wrote:
>
> >On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf
> wrote:
>
> >> On Saturday, 19 October, 2019 18:26, Petr Jakeš <
> petr.jakes@gmail.com> wrote:
>
> >>> After long time I have
On Sunday, 20 October, 2019 06:58, Petr Jakeš wrote:
>On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf wrote:
>> On Saturday, 19 October, 2019 18:26, Petr Jakeš
>> wrote:
>>> After long time I have set up development environment properly and I
>>> am able to start to study your queries.
>>> I
On Sun, Oct 20, 2019 at 2:53 AM Keith Medcalf wrote:
> On Saturday, 19 October, 2019 18:26, Petr Jakeš
> wrote:
>
> >After long time I have set up development environment properly and I am
> >able to start to study your queries.
>
> >I am lost. I don't either understand the first bunch of subque
On Saturday, 19 October, 2019 18:26, Petr Jakeš
wrote:
>After long time I have set up development environment properly and I am
>able to start to study your queries.
>I am lost. I don't either understand the first bunch of subqueries... (
>What is returned in the "ratetoprior"? I have been pull
After long time I have set up development environment properly and I am
able to start to study your queries.
I am lost. I don't either understand the first bunch of subqueries... (
What is returned in the "ratetoprior"? I have been pulling my hair over 3
hours trying to figure it out ... no clue w
#x27;s a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Keith Medcalf
>Sent: Wednesday, 9 October, 2019 13:04
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT uses inde
transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table nodes(name text not null collate nocase unique);
sqlite> insert into nodes values ('dangtalk');
sqlite> insert into nodes values ('dingdong');
sqlit
> On Oct 9, 2019, at 10:02 AM, Keith Medcalf wrote:
>
> SUBSTR(name, 0, ?) is an expression, so unless you have an index on that
> expression, then an index cannot be used to SEARCH for the rows.
That's accurate in general. However, there _is_ a very similar special-case
optimization for the
hway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Brannon King
>Sent: Tuesday, 8 October, 2019 15:53
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] SELECT uses in
I have this query:
UPDATE nodes SET parent = ? WHERE SUBSTR(name, 0, ?) = ?
EXPLAIN QUERY PLAN tells me that it is going to do a table scan. At the
same time, the query plan for this:
SELECT * FROM nodes WHERE SUBSTR(name, 0, ?) = ?
tells me that it can and will use the (primary key) index on the n
On Monday, 2 September, 2019 12:26, Petr Jakeš wrote:
>Yes, you are right. The error is connected with the version of
>SQLite. Now I am trying to build DB Browser using SQLite version 3.29.0.
>Than I have to study your code. Your knowledge and SQL Windows
>functions are over my scope. Thank for
Yes, you are right. The error is connected with the version of SQLite. Now
I am trying to build DB Browser using SQLite version 3.29.0.
Than I have to study your code. Your knowledge and SQL Windows functions
are over my scope. Thank for the study material for next weekend :D
On Mon, Sep 2, 2019
On Monday, 2 September, 2019 10:34, Petr Jakeš wrote:
>Wow, this is HUUUDGE !!!
>Thanks!
>What editor are you using, btw?
Typically this is on Windows 10 (for Workstations) and the editor I use is TSE
(The Semware Editor). Started using TSE under OS/2 way back and I like it a
lot and have u
Wow, this is HUUUDGE !!!
Thanks!
What editor are you using, btw? I am on Linux Mint and trying your queries
with "SQLite Studio" and "DB Browser for SQLite" is throwing syntax error
(I think because of the rows
"lead(timestamp) over (order by timestamp) as next_timestamp,"
From the sqlite3 comma
Of course, what we are emulating here is called a "Process Historian", common
examples being PHD and PI. So, if you make a few minor adjustments, you can
make this run just about as fast as a "designed for purpose" Process Historian.
The changes are that you need to store the data in an "econo
This will get you the consumption projection for each day in the table
(timestamp in s represents the ENDING period you are interested in and you can
modify it to whatever interval you want, and of course the final query gets the
result). It works by computing the slope from each timestamp to
As far I have ended with following:
WITH miniPow as (
select date(TIMESTAMP,'+1 day') as d, max(TOTAL_KWH) mini
from power
group by date(timestamp)
)
, maxiPow as (
select date(TIMESTAMP) as d, max(TOTAL_KWH) maxi
from power
group by date(timestamp)
)
select maxiPow.d, ROUND(maxi-mini, 1) from min
I am storing electricity consumption data to the sqlite.
The simple table to store kWh consumption looks like following example
(accumulated total readings in each row - exactly as you see on your
electricity meter):
|ID|timestamp|kWh ||1 | 2019-07-31 14:24:25 | 270.8||2 |
2019-07-3
Thank you, Adrian. I think this is reason changes() exist.
Roman
Sent from my T-Mobile 4G LTE Device
Original message
From: Adrian Ho
Date: 6/15/19 12:25 AM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] select within transaction
On 15/6/19 2
On 15/6/19 2:22 AM, Roman Fleysher wrote:
> I have a transaction consisting of two commands: update and select. The idea
> is to get new state after update:
>
> PRAGMA busy_timeout = 50;
> BEGIN EXCLUSIVE;
> UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
> SELECT d FROM t WHERE c = 5 AND ...;
> COMMIT
On 15/6/19 3:06 AM, Jose Isaias Cabrera wrote:
> Jose Isaias Cabrera, on Friday, June 14, 2019 02:50 PM, wrote...
>
>> Yes, and no. From what I understand, and have been using it, if
>> something was written to the DB, it will give you a 1. Otherwise
>> a 0. But, it is not the amount of fields,
ubject: Re: [sqlite] select within transaction
How are you sending the commands to the cli?
If you're doing...
sqlite3 myfile.sqlite ".read somefile.sql"
...then you can start the sql file with...
.bail on
...and as soon as it hits an error it will stop there and not continue
pr
tion. It does give you the amount of fields updated. Ie.
sqlite> create table a (a, b, c);
sqlite> insert into a values (1, 2, 3);
sqlite> insert into a values (2, 3, 4);
sqlite> insert into a values (3, 4, 5);
sqlite> select changes();
1
sqlite> select total_changes();
3
sqlite>
es it will rollback the uncommitted transaction.
-Original Message-
From: sqlite-users On Behalf Of
Roman Fleysher
Sent: Friday, June 14, 2019 2:23 PM
To: General Discussion of SQLite Database
Subject: [sqlite] select within transaction
Dear SQLiters,
I am using sqlite3 shell.
I h
able a (a, b, c);
sqlite> create table b (a, d, e);
sqlite> insert into a values (1, 2, 3);
sqlite> insert into a values (2, 3, 4);
sqlite> insert into a values (3, 4, 5);
sqlite> select changes(); -- this is for the last write
1
sqlite> select total_changes(); -- this is for t
Dear SQLiters,
I am using sqlite3 shell.
I have a transaction consisting of two commands: update and select. The idea is
to get new state after update:
PRAGMA busy_timeout = 50;
BEGIN EXCLUSIVE;
UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
SELECT d FROM t WHERE c = 5 AND ...;
COMMIT;
Is this wha
...> dancename text
...> );
sqlite> insert into songfiletable (dancename) values ('Waltz');
sqlite> select * from songfiletable where dancename like 'Waltz';
1|Waltz
sqlite> select * from songfiletable where dancename = 'Waltz';
1|Waltz
Works for me
On 4 Jun 2019, at 12:46am, Doug wrote:
> Why does the "=" query fail and the "like" query work?
To help us investigate ...
Which version of SQLite is this ? You can use
SELECT sqlite_version();
to find out.
What do you mean by 'work' and 'fail' ? Are you referring to an error code ?
I am using Sqlite under QT 5.12.0.
Why does the "=" query fail and the "like" query work? There are no
wildcards involved.
I create the table this way:
QString sqlcreate = QLatin1String(
"CREATE TABLE songfiletable ("
"songfile_id INTEGER PRIMARY KEY,"
"dancename TEXT"
> ON table1.rowid = table2.rowid
> >> WHERE table1.name LIKE '%smth%'
> >>
> >>
> >> -Original Message-----
> >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org
> ]
> >> On Behalf Of Simon Slavin
> &g
table1
>> ON table1.rowid = table2.rowid
>> WHERE table1.name LIKE '%smth%'
>>
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Simon Slavin
>> Sent: Friday, September 14,
gt; WHERE table1.name LIKE '%smth%'
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Simon Slavin
> Sent: Friday, September 14, 2018 1:59 PM
> To: SQLite mailing list
> Subject: Re: [s
to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Maziar Parsijani
>Sent: Friday, 14 September, 2018 12:32
>To: sqlite-users@mai
Hi,
Thanks for your answer.I used your answer like this :
SELECT * FROM table2
JOIN table1 on table1.rowid = table2.rowid
WHERE table1.name LIKE '%smth%'
Because without the "table1 on" statement it didn't work .
On Fri, Sep 14, 2018 at 10:29 PM Simon Slavin wrote:
> On 14 Sep
To: SQLite mailing list
Subject: Re: [sqlite] [SQLITE]select from a table and use its data to select
from another one
On 14 Sep 2018, at 6:50pm, Maziar Parsijani wrote:
> I have 2 tables with the same rowid now I want to :
> select rowid from table1 where table1 like "%smth%"
&g
On 14 Sep 2018, at 6:50pm, Maziar Parsijani wrote:
> I have 2 tables with the same rowid now I want to :
> select rowid from table1 where table1 like "%smth%"
> select * from table2 where rowid =(selected rows before)
>
> I mean if I could do it in a same query.
This is what JOIN is for.
S
Hi,
I have 2 tables with the same rowid now I want to :
select rowid from table1 where table1 like "%smth%"
select * from table2 where rowid =(selected rows before)
I mean if I could do it in a same query.
___
sqlite-users mailing list
sqlite-users@maili
Do I correctly understand the intention of the UPDATE is that for
each my_id in meta_table, it will store the count of all the hashes that
are associated only with my_id and no other id's?
In that case, have you tried:
UPDATE
meta_table
SET
distinct_hashes = (
SELECT
Hi List,
I'm trying to find all hashes that are unique to a specific id (my_id),
and then use a UPDATE-Join to update another table with that number.
After much tweaking, I've simplified the table down to a basic temp
table (actually created using a CREATE AS SELECT ... GROUP BY my_id, hash):
2018-04-12 21:09 GMT+02:00 Csányi Pál :
> Thank you very much for the help and for the explanations.
>
> Waw! It is so complicated at first! I hope I shall understand these soon.
>
> Finally I decide to use this query:
> SELECT Keltezes FROM Orak WHERE Keltezes >= date('now','localtime')
> ORDER BY
Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>>Sent: Thursday, 12 April, 2018 10:24
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>On 12 Apr 2018,
l but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Thursday, 12 April, 2018 10:24
>To: SQLite mailing list
>Subje
On 12 Apr 2018, at 5:16pm, R Smith wrote:
> SELECT MIN(TheDate) -- get the smallest date
> FROM Orak -- from the table with School-days
> WHERE TheDate >= date('now') -- where the school-day is later or equal to
> today.
> ;
This reflects exactly the right s
You're right.
I am developing an Android app on App Inventor2.
The app is in Hungarian language so the SQLite database contains
tables and columns with Hungarian names.
The whole schema is like this:
CREATE TABLE Beiratkozottak(
az INTEGER PRIMARY KEY UNIQUE,
TanuloNeve TEXT NOT NULL,
ere exists (select 1 from Dates where TheDate = tempDate)
)
select max(tempDate) as TheDate from foo;
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Csányi Pál
Sent: Thursday, April 12, 2018 11:36 AM
To: SQLite mailing list
Su
says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Thursday, 12 April, 2018 09:36
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
&g
Maybe something like: SELECT MIN(thedate) FROM dates WHERE thedate >=
date('now');
On 4/12/18, 11:05 AM, "sqlite-users on behalf of Peter Da Silva"
wrote:
Ah, so if there's two days in a row that aren't school days, you need to be
able to select a day two or more days in the future.
Ah, so if there's two days in a row that aren't school days, you need to be
able to select a day two or more days in the future.
On 4/12/18, 11:02 AM, "sqlite-users on behalf of Csányi Pál"
wrote:
So when I start the android app on my phone it should display the
school day at that day
On 2018/04/12 5:35 PM, Csányi Pál wrote:
Thank you very much!
Just can't understand why the CASE method does not work?
It can't be done with the CASE expression at all?
The CASE expression modifies a single line, the WHERE clause restricts
the selection to the lines that qualify.
So if you
Hi Ryan,
2018-04-12 17:36 GMT+02:00 R Smith :
> On 2018/04/12 5:20 PM, Csányi Pál wrote:
>>
>> Yes, this is what I am asking.
>>
>> 2018-04-12 17:17 GMT+02:00 Keith Medcalf :
>>>
>>> Which seems like a rather long winded way of stating the problem:
>>> "I have a table with a bunch-o-dates in it.
-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
>Sent: Thursday, 12 April, 2018 09:26
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>
>Then Richard is correct (of course) ... which is a perfect
>t
On 2018/04/12 5:20 PM, Csányi Pál wrote:
Yes, this is what I am asking.
2018-04-12 17:17 GMT+02:00 Keith Medcalf :
Which seems like a rather long winded way of stating the problem:
"I have a table with a bunch-o-dates in it. I want a query which will return, at
the time the query is run, base
c volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>Sent: Thursday, 12 April, 2018 09:20
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CAS
ut only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Thursday, 12 April, 2018 09:20
>To: SQLite mailing list
>Subject: Re
.
>
>
>>-Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>>Sent: Thursday, 12 April, 2018 09:10
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>
re's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
>Sent: Thursday, 12 April, 2018 09:10
>To: S
On 4/12/18, Csányi Pál wrote:
> 2018-04-12 17:08 GMT+02:00 Keith Medcalf :
>>
>> select TheDate from Dates where TheDate == date('now');
>
> Yes, but I want the CASE because if there is no such date in the Dates
> table which is equal to the date('now') then it should return the
> date('now','+1 d
2018-04-12 17:08 GMT+02:00 Keith Medcalf :
>
> select TheDate from Dates where TheDate == date('now');
Yes, but I want the CASE because if there is no such date in the Dates
table which is equal to the date('now') then it should return the
date('now','+1 day').
sqlite.org] On Behalf Of Csányi Pál
>Sent: Thursday, 12 April, 2018 09:06
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>2018-04-12 17:00 GMT+02:00 Peter Da Silva
>:
>> One of the lines of the output does indeed have '2018-04-12' as
>expecte
2018-04-12 17:00 GMT+02:00 Peter Da Silva :
> One of the lines of the output does indeed have '2018-04-12' as expected.
Indeed, I did not notice.
Then how can I get only that date from the Dates table - which is
equal to the current date?
___
sqlite-user
One of the lines of the output does indeed have '2018-04-12' as expected.
On 4/12/18, 9:59 AM, "sqlite-users on behalf of Csányi Pál"
wrote:
2018-04-12 16:51 GMT+02:00 Peter Da Silva :
> You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which
is what you're getting, n
2018-04-12 16:51 GMT+02:00 Peter Da Silva :
> You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is
> what you're getting, no?
Yes, indeed.
But I thought the first part would be done:
CASE TheDate WHEN date('now') THEN TheDate
that is, if the TheDate is = date('now') THEN
it
You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is what
you're getting, no?
On 4/12/18, 9:47 AM, "sqlite-users on behalf of Csányi Pál"
wrote:
Hi Simon,
2018-04-12 14:32 GMT+02:00 Simon Slavin :
> On 12 Apr 2018, at 1:25pm, Csányi Pál wrote:
>
Hi Simon,
2018-04-12 14:32 GMT+02:00 Simon Slavin :
> On 12 Apr 2018, at 1:25pm, Csányi Pál wrote:
>
>> SELECT CASE TheDate = date('now') WHEN TheDate ...
>
> I don't think that's what you wanted. Perhaps
>
> SELECT CASE TheDate WHEN date('now') ...
>
> But you should test the output of "date('n
On 12 Apr 2018, at 1:25pm, Csányi Pál wrote:
> SELECT CASE TheDate = date('now') WHEN TheDate ...
I don't think that's what you wanted. Perhaps
SELECT CASE TheDate WHEN date('now') ...
But you should test the output of "date('now')" to make sure it is in the
format you want.
Simon.
Hi,
I have a small database:
DatesOfYear.db
with only one table:
CREATE TABLE Dates(id integer PRIMARY KEY UNIQUE, TheDate date NOT NULL);
I insert into the table some datas with:
INSERT INTO Dates VALUES(1,'2018-04-01');
INSERT INTO Dates VALUES(2,'2018-04-02');
INSERT INTO Dates VALUES(3,'20
On 31 Mar 2018, at 2:04pm, Koen Amant wrote:
> there is a service running in the background who adds records
> to the database (POS system) I can't stop this service and all the new
> records that are added I can't see in my query result. It's like the
> database is locked for other users and so
I have a table with 9 records. When I run 'SELECT * FROM table;' in my
VB.Net I only get 6 from the 9 records. When I run the query in 'DB Browser'
an SQLite viewer I get them all.
The thing is there is a service running in the background who adds records
to the database (POS system) I can't stop
You saved my bacon with this one. Just wanted to pop in and say a quick
thanks to you. :)
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mail
On 19 Dec 2017, at 8:37pm, zakari wrote:
> pasting some logs, Im declaring again this happening only the first time,
> afterwards working without problem.
> 2017-12-17 15:16:23 - execute
> 2017-12-17 15:17:20 - executed
>
> 2017-12-19 14:53:35 - execute
> 2017-12-19 14:54:32 - executed
>
> 20
hi all,
I have exactly the same problem with topic :
http://sqlite.1065341.n5.nabble.com/SELECT-query-first-run-is-VERY-slow-td33100i20.html
--
The dbase sitting on linux server, Im accessing the dbase with PDO object.
-connected
-prepare the statement
-execute
here makes =>1min lag, *only the
On 21.11.2017 15:36, Richard Hipp wrote:
I'll be working on some other solution for you.
Many thanks, but this is not necessary. I can rebuild from Fossil.
Ralf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.s
On 11/21/17, Richard Hipp wrote:
>
> To work around this problem, please DROP all indexes on the INTEGER
> PRIMARY KEY columns.
Except, you don't have any indexes on INTEGER PRIMARY KEY columns. I
misread the schema.
I'll be working on some other solution for you.
--
D. Richard Hipp
d...@sqli
On 11/20/17, David Raymond wrote:
>
> To reproduce, download this database file (5.6MB, SHA1
> 12d1295d06327ee19ed2453517b0dd83233c6829, available for two days from now):
>
>https://expirebox.com/download/328baafe26688579fccd55debfc54ad3.html
>
> This SQL returns a single result row with a val
Apologies for the Spam, and this may be of no importance whatsoever, but
just in case it is useful...
I already mentioned that dropping/messing with the sqlite_stat1 table
doesn't help - BUT it seems if you close the connection and re-open in a
new connection (after you have dropped the sqlite
Just to Add to what Ralf and David already pointed out:
Works for me on 3.18, not in 3.20.1 and more importantly, the
sqlite_stat1 table itself seems to have zero impact, once Analyze is
run, the query always does not work, even if you drop the sqlite_stat1
table or mess with its values.
H
3.18.0 gets it correct, 3.19.0 gets it wrong.
-Original Message-
From: David Raymond
Sent: Monday, November 20, 2017 11:03 AM
To: 'SQLite mailing list'
Subject: RE: [sqlite] SELECT result different after ANALYZE
Confirming it's doing the same thing for me. Taking o
nter ".help" for usage hints.
sqlite> .timer off
sqlite> .eqp off
sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|t1|t1|2|CREATE TABLE t1 (id integer primary key, t2_id integer)
index|t1_1|t1|738|CREATE INDEX t1_1 on t1 (t2_id asc)
table|t2|t2|1409|CRE
I am presenting a scenario where a SELECT produces a different result
after running ANALYZE.
To reproduce, download this database file (5.6MB, SHA1
12d1295d06327ee19ed2453517b0dd83233c6829, available for two days from now):
https://expirebox.com/download/328baafe26688579fccd55debfc54ad3.htm
usesStmtJournal=0
11Goto 0 1 000
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Thomas Flemming
Sent: Wednesday, May 31, 2017 5:59 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [
Thanks guys for all the information.
Now I know, how to proceed.
Tom
:)
Am 31.05.2017 um 22:02 schrieb R Smith:
On 2017/05/31 9:31 PM, Thomas Flemming wrote:
Hi,
maybe, hopefully, I missed something, its still about this database:
http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
On 2017/05/31 9:31 PM, Thomas Flemming wrote:
Hi,
maybe, hopefully, I missed something, its still about this database:
http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
Copying just the ids from 12mio records ordered in a temp-table takes
60 seconds. There is a COLLATE NOCASE index
On 31 May 2017, at 8:31pm, Thomas Flemming wrote:
> Copying just the ids from 12mio records ordered in a temp-table takes 60
> seconds. There is a COLLATE NOCASE index on label.
>
> Is this normal or can this also be done faster?
>
> DROP TABLE IF EXISTS RowCursor;
> CREATE TEMP TABLE RowCurs
Hi,
maybe, hopefully, I missed something, its still about this database:
http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
Copying just the ids from 12mio records ordered in a temp-table takes 60
seconds. There is a COLLATE NOCASE index on label.
Is this normal or can this also be
> Then what is "FROM Pois_bb, Pois WHERE...Pois_bb.Id = Pois.Id"?
> That's joining two tables together.
This is just because of the rtree, which is in Pois_bb
(http://www.sqlite.org/rtree.html), has nothing to do with the second
condition "styleid IN .."
Am 30.05.2017 um 18:29 schrieb David
Thomas Flemming Tue, 30 May 2017 09:43:15 -0700
>> Try putting a "+" symbol before "styleid". Like this:
>>
>> AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762)
> THATS IT !! :-)))
>
> 50ms with +, and 15000ms without the +
>
> How is that possible?
Hello, best
If you scroll down in my previous reply I put the explain query plan outputs in
with the queries. Guess I should have mentioned that. (Re-copied them below)
It was using the index on StyleId, thinking that was going to be faster. What
Dr Hipp suggested in adding the unary + operator does is turn
> Try putting a "+" symbol before "styleid". Like this:
>
> AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762)
THATS IT !! :-)))
50ms with +, and 15000ms without the +
How is that possible?
Am 30.05.2017 um 17:36 schrieb Richard Hipp:
On 5/27/17, Thomas Flemmin
On 5/27/17, Thomas Flemming wrote:
> Hi,
>
> I have a table Pois with points of interest (geogr. coordinate, label,
> styleid) where I do regional querys using a rtree-index:
>
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND y1 > -15.12862
>AND x0
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Thomas Flemming
Gesendet: Dienstag, 30. Mai 2017 18:15
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow
force it to go the way you want by using "cross joi
:15
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow
> force it to go the way you want by using "cross join" to force the
> ordering of
How would such "cross join" statemant look like?
Am 30.05.2017 um 1
:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Thomas Flemming
Sent: Monday, May 29, 2017 9:28 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition slow
Ok, here is a sample to try these queries:
http://files.qvgps.com/0-tom-
s join" to force the ordering of the join.
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Thomas Flemming
Sent: Monday, May 29, 2017 9:28 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SELECT WHERE with RTREE
On May 30, 2017 10:07:45 AM EDT, Thomas Flemming wrote:
>Style.Id doesn't need to be LONG, you're right. I changed it but it
>doesn't
>make a difference.
>Pois.Id need to be LONG because the source for this column is really
>containing 64-bit values
Integers in SQLite are of variable size; if
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Thomas Flemming
Sent: Tuesday, May 30, 2017 8:08 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition slow
> > Do yo
to:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Thomas Flemming
Gesendet: Dienstag, 30. Mai 2017 16:08
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow
> Do you know which SQLite version is being used by SQLite Expert
1 - 100 of 1050 matches
Mail list logo