On Tue, 5 Feb 2019 at 22:46, Simon Slavin wrote:
> On 5 Feb 2019, at 8:59am, Rowan Worth wrote:
>
> > SELECT source1, source2, ts, value
> > FROM rolling
> > WHERE source1 = 'aaa'
> > AND ts > 1 AND ts < 1
> > ORDER BY source1, source2, ts;
> >
> > And this index:
> >
> > CREATE INDEX `
On 2019/02/06 12:12 AM, Gerlando Falauto wrote:
The use case involves retaining as much data as the storage can possibly
hold (so a bunch of gigabytes).
I could've just used directories and logfiles instead of abusing a
relational database but I just thought it would more convenient to issue a
On Wed, Feb 6, 2019 at 11:26 AM Keith Medcalf wrote:
"you have not normalized the data before storing it"
This is true of most of the hundreds, if not thousands, of schema that I
have seen.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.
On Tuesday, 5 February, 2019 15:12, Gerlando Falauto wrote:
> I could've just used directories and logfiles instead of abusing
> a relational database but I just thought it would more convenient
> to issue a query and use a cursor.
Well, the "abusing a relational database" is the correct termino
On 5 Feb 2019, at 10:12pm, Gerlando Falauto wrote:
> I actually started off with source1,source2,ts as the primary key and for
> some reason (which I no longer remember) I thought it would be wise to use a
> ROWID and add an index instead.
That is probably the right solution. There are reason
Hi Ryan,
first of all thank you for your patience and contribution.
[]
>
> Add to that the fact that an SQLite TABLE is, in and of itself, nothing
> less than a covering Index with row_id as a key (or a custom key for
> WITHOUT ROWID tables), and as such it is a rather good Index and a
> mos
On 2019/02/05 4:46 PM, Simon Slavin wrote:
On 5 Feb 2019, at 8:59am, Rowan Worth wrote:
What is stopping sqlite's query planner from taking advantage of the index,
which it has chosen to use for the query, to also satisfy the ORDER BY?
I suspect that, given the data in the table, the inde
On 5 Feb 2019, at 8:59am, Rowan Worth wrote:
> SELECT source1, source2, ts, value
> FROM rolling
> WHERE source1 = 'aaa'
> AND ts > 1 AND ts < 1
> ORDER BY source1, source2, ts;
>
> And this index:
>
> CREATE INDEX `sources` ON `rolling` (
>`source1`,
>`source2`,
>`ts`
> );
Hi Rowan,
thank you for your kind support. You grasped the essence of my questions.
:-)
I'm using SQLite 3.25.00.
Thank you,
Gerlando
On Tue, Feb 5, 2019 at 9:59 AM Rowan Worth wrote:
> On Tue, 5 Feb 2019 at 16:06, Simon Slavin wrote:
>
> > On 5 Feb 2019, at 8:00am, Gerlando Falauto
> > w
On Tue, 5 Feb 2019 at 16:06, Simon Slavin wrote:
> On 5 Feb 2019, at 8:00am, Gerlando Falauto
> wrote:
>
> > Thank you for your explanations guys. All this makes perfect sense.
> > I still can't find a solution to my problem though -- write a query that
> is guaranteed to return sorted results,
On 5 Feb 2019, at 8:00am, Gerlando Falauto wrote:
> Thank you for your explanations guys. All this makes perfect sense.
> I still can't find a solution to my problem though -- write a query that is
> guaranteed to return sorted results, in some optimal way.
Please state your table definition, a
Thank you for your explanations guys. All this makes perfect sense.
I still can't find a solution to my problem though -- write a query that is
guaranteed to return sorted results, in some optimal way.
Any suggestion welcome.
Thank you,
Gerlando
Il lun 4 feb 2019, 22:24 Simon Slavin ha scritto:
On 4 Feb 2019, at 9:14pm, James K. Lowden wrote:
> As Keith said, SQLite allows ORDER BY in subqueries. The SQL standard does
> not.
True. But SQLite does not guarantee that the outer query will preserve the
inner query's ORDER BY, even if the outer query doesn't have its own ORDER BY.
S
On Mon, 4 Feb 2019 18:55:33 +0100
Gerlando Falauto wrote:
> I remember reading ORDER BY is only allowed in
> the outer query
As Keith said, SQLite allows ORDER BY in subqueries. The SQL standard
does not.
Logically, ORDER BY makes sense only for the outer query. An SQL
SELECT statement decr
>I wonder if I'd be allowed to add an ORDER BY in the subquery and if
>that would make any difference -- I remember reading ORDER BY is only
>allowed in the outer query (which makes perfect sense).
Yes, you can use an order by in a subquery (either a correlated subquery or a
table generating sub
On Mon, Feb 4, 2019 at 4:52 PM Simon Slavin wrote:
> On 4 Feb 2019, at 1:55pm, Gerlando Falauto
> wrote:
>
> > Or (most likely) my understanding of how data is retrieved is plain
> wrong...
>
> Or your understanding how the current version of SQLite is correct, but a
> later version of SQLite wi
Hi Luuk,
It says:
>
> SQLite *attempts* to use an index to satisfy the ORDER BY clause of a
> query when possible
>
>
> To be (abolutely!) SURE results are in the correct order, you need an
> ORDER BY.
>
No questioning about that. ORDER BY *must* be there in order to get the
results correctly sor
On 4 Feb 2019, at 1:55pm, Gerlando Falauto wrote:
> Or (most likely) my understanding of how data is retrieved is plain wrong...
Or your understanding how the current version of SQLite is correct, but a later
version of SQLite will have different optimizations and do things differently.
So at
On 4-2-2019 14:55, Gerlando Falauto wrote:
Thank you Luuk, I understand your point.
However, the query plan already takes advantage of the index and should be
retrieving data in that order.
Reading the docs
https://www.sqlite.org/optoverview.html#order_by_optimizations my
understanding was that
Thank you Luuk, I understand your point.
However, the query plan already takes advantage of the index and should be
retrieving data in that order.
Reading the docs
https://www.sqlite.org/optoverview.html#order_by_optimizations my
understanding was that
SQLite would be taking advantage of that.
So p
On 3-2-2019 23:29, Gerlando Falauto wrote:
IMHO, adding the ORDER BY clause to query 1) above (i.e. query 2) should
ideally yield the exact same query plan.
In the end adding an ORDER BY clause on the exact same columns of the index
used to traverse the table, should be easily recognizable.
Know
ite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Gerlando Falauto
> >Sent: Saturday, 2 February, 2019 15:20
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Min/Max and skip-scan optimizations
> >
> >Hi,
> >it's me again, struggling w
ists.sqlite.org] On Behalf Of Gerlando Falauto
> >Sent: Saturday, 2 February, 2019 15:20
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Min/Max and skip-scan optimizations
> >
> >Hi,
> >it's me again, struggling with indices once again.
> >What I&#
On 3 Feb 2019, at 9:52am, Gerlando Falauto wrote:
> I do want them sorted, and I also want the whole (huge) dataset to be
> processable without having to store it all in memory or temp files.
> Sounds like the whole purpose of an index, doesn't it?
> I do know SQL is all about the result, not how
Simon, Tim,
Il sab 2 feb 2019, 23:40 Simon Slavin ha scritto:
> On 2 Feb 2019, at 10:19pm, Gerlando Falauto
> wrote:
>
> > Results should be naturally ordered by source1, source2,ts.
>
> [Sorry, I missed this the first time. Thanks, Tim.]
>
> Sorry, no. You're making assumptions about how SQL
lists.sqlite.org] On Behalf Of Gerlando Falauto
>Sent: Saturday, 2 February, 2019 15:20
>To: SQLite mailing list
>Subject: Re: [sqlite] Min/Max and skip-scan optimizations
>
>Hi,
>it's me again, struggling with indices once again.
>What I'm now trying to do is fil
On 2 Feb 2019, at 10:19pm, Gerlando Falauto wrote:
> Results should be naturally ordered by source1, source2,ts.
[Sorry, I missed this the first time. Thanks, Tim.]
Sorry, no. You're making assumptions about how SQLite works internally. If
you want your results sorted, ask for them sorted.
On 02 Feb 2019, at 22:19, Gerlando Falauto wrote:
> What I'm now trying to do is filter on source1 and by range of timestamp.
> Results should be naturally ordered by source1, source2,ts.
Not unless you use an ORDER BY.
--
Cheers -- Tim
___
sqlite
On 2 Feb 2019, at 10:19pm, Gerlando Falauto wrote:
> SELECT source1, source2, ts, value
> FROM rolling
> WHERE source1 = 'aaa'
> AND ts > 1 AND ts < 10;
>
> [...snip...]
> So I add an extra ORDER BY clause:
Concentrate on the results you want. Don't try to 'game' the library. You
don't unde
Hi,
it's me again, struggling with indices once again.
What I'm now trying to do is filter on source1 and by range of timestamp.
Results should be naturally ordered by source1, source2,ts.
1)
SELECT source1, source2, ts, value
FROM rolling
WHERE source1 = 'aaa'
AND ts > 1 AND ts < 10;
QUERY PLAN
YES! Thank you!
Many thanks for the ".eqp full" tip also, that really explains a lot
(though I don't really understand any of it yet).
Have a great day!
Gerlando
On Mon, Jan 28, 2019 at 6:50 AM Keith Medcalf wrote:
>
> Do you perhaps want this:
>
> select source1,
>source2,
>(
Do you perhaps want this:
select source1,
source2,
(
select min(ts)
from rolling
where source1 = x.source1
and source2 = x.source2
)
from (
select distinct source1,
source2
from rolling
Hi,
I have a database table looking like this:
CREATE TABLE `rolling` (
`source1`TEXT NOT NULL,
`source2`TEXT NOT NULL,
`ts`INTEGER NOT NULL,
`value`TEXT
);
CREATE INDEX `sources` ON `rolling` (
`source1`,
`source2`,
`ts`
);
INSERT INTO rolling
WI
33 matches
Mail list logo
