Re: [sqlite] calculated-value indexes are not covering?

[Wout Mertens]

Nico, I respectfully disagree, if you look at my first post you can see
that the first query does consider that single value index on s covering.
Indeed all the indexes here have all the required data to be covering for
their queries.

As David says, it seems there is a missed optimization opportunity here.
When looking at the EXPLAIN output, the non-covering index version does not
seem to actually use the table value it is copying, but I'm having a hard
time decyphering it.

On large tables, this is the difference between a 4ms search and a 50ms
search…

On Wed, Aug 9, 2017, 9:29 PM Nico Williams  wrote:

> On Wed, Aug 09, 2017 at 06:59:18PM +, Wout Mertens wrote:
> > but… index s is covering and only includes the field s? I thought a
> > covering index was one where all the data needed to satisfy the query is
> in
> > index? I would say that all the indexes here conform to that definition?
>
> No, "covering" means that the columns listed in the index include all
> the columns from the source table that you need for a given query:
>
>   CREATE TABLE t(j TEXT, s TEXT, foo TEXT);
>   SELECT s, j FROM t WHERE s = 'foo'; -- full table scan bc [s] is not
>   -- indexed, is not a PRIMARY KEY,
>   -- and is not UNIQUE
>   CREATE INDEX t1 ON t(s);
>   SELECT s FROM t WHERE s = 'foo';-- uses index; index covers column
>   -- selection (just [s])
>
>   SELECT s, j FROM t WHERE s = 'foo'; -- full table scan unless [s] is
>   -- a PRIMARY KEY
>
>   CREATE INDEX t2 ON t(j, s);
>   SELECT s, j FROM t WHERE s = 'foo'; -- full table scan; t2 doesn't
>   -- help because we need a covering
>   -- index where [s] is a prefix
>
>   CREATE INDEX t3 ON t(s, j);
>   SELECT s, j FROM t WHERE s = 'foo'; -- uses covering index t3, finally
>
>   SELECT s, j, foo FROM t WHERE s = 'foo'; -- t3 does not cover -> full
>-- table scan
>
> Usually you should have a PRIMARY KEY, and if [s] were one here, then
> none of these would need full table scans, but only two of these would
> use only an index and not also index the table via the PK.
>
>   -- truly covering index, but only usable in queries by [s] or [s],
>   -- [j], or [s], [j], [foo]:
>   CREATE INDEX t4 ON t(s, j, foo);
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailinglist question

[Wout Mertens]

The one sad thing about this really wonderful mailing list is that its
archives seem to rank really low in Google searches. Whenever I search for
something, I've not encountered the archives with answers yet.

Google groups has all the features requested here. I wonder if it can't be
set up as a gateway to this mailing list? So subscribe Google to here and
vice versa; when someone sends email to the Google Group it would arrive on
this list (with a different From address but I suppose that's alright)

On Thu, Aug 10, 2017, 2:54 AM Simon Slavin  wrote:

> Archive of this list, with less than 24 hours lag, here:
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailinglist question

[Simon Slavin]

Archive of this list, with less than 24 hours lag, here:



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailinglist question

[Wolfgang Enzinger]

Am Wed, 9 Aug 2017 17:02:42 -0500 schrieb Nico Williams:

> I... would like to use NNTP, but I'd need someone to provide the
> service.

I'm connected to this mailing list as if it was a newsgroup using
nntp://news.gmane.org:563/gmane.comp.db.sqlite.general. No authentication
required. Of course, for posting you have to be subscribed to the list.
(all info AFAIK)

HTH Wolfgang

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailinglist question

[jungle Boogie]

On 9 August 2017 at 15:02, Nico Williams  wrote:
> On Wed, Aug 09, 2017 at 03:48:34PM -0600, Keith Medcalf wrote:
>> There is a solution since about 1984 ... it is called NNTP (Usenet
>> News).  Google Groups is basically Usenet News with a (so some people
>> thing -- but not I -- I detest so-called web-forums) purty front end
>> to the news reader.  Gateways between mailman, bitnet, usenet, and a
>> bunch of other stuff existed for a long time (and still do, mostly).
>
> I... would like to use NNTP, but I'd need someone to provide the
> service.  Though I'd be annoyed at having to use two different UAs (I
> use mutt primarily; it doesn't support NNTP).
>

Here's a usenet provider:
https://www.eternal-september.org
https://en.wikipedia.org/wiki/Eternal_September

I think mutt can be patched to support nntp.
I've used slrn and it's pretty straight forward to use, but I
understand the switch clients issue.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailinglist question

[Rich Shepard]

On Wed, 9 Aug 2017, Nico Williams wrote:


Another thing I've done in the past is: download list archives, run a
script to fix the From lines so the archive is then a proper mbox format,
then use mutt. :) It helps if the archives keep Message-ID headers.


Nico,

  I've not followed this thread so my comment might be way off-base.
However, ... I use alpine (and pine before that) which uses mbox for
messages, with a direct subscription to the mail list. I run postfix here
but need to now relay outboun messages through my ISP since Frontier
Communications no longer supports my static IP address and I'm forced to use
their DHCP server. Inbound messages, however, come directly in and are
stored in ~/mail/sqlite.

Rich
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailinglist question

[Nico Williams]

On Wed, Aug 09, 2017 at 03:48:34PM -0600, Keith Medcalf wrote:
> There is a solution since about 1984 ... it is called NNTP (Usenet
> News).  Google Groups is basically Usenet News with a (so some people
> thing -- but not I -- I detest so-called web-forums) purty front end
> to the news reader.  Gateways between mailman, bitnet, usenet, and a
> bunch of other stuff existed for a long time (and still do, mostly).

I... would like to use NNTP, but I'd need someone to provide the
service.  Though I'd be annoyed at having to use two different UAs (I
use mutt primarily; it doesn't support NNTP).

On the other hand, anyone running a mailing list could easily provide a
read-only IMAP service given a suitable implementation.  That would be
easy enough for users, but the MUA bits are missing.  (Sadly, very
little work is being done on MUAs.)  Providing a mostly-read-only IMAP
service would be possible, but it requires at least a modicum of account
provisioning.

What a mess.

Another thing I've done in the past is: download list archives, run a
script to fix the From lines so the archive is then a proper mbox
format, then use mutt.  :)  It helps if the archives keep Message-ID
headers.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailinglist question

[Keith Medcalf]

There is a solution since about 1984 ... it is called NNTP (Usenet News).  
Google Groups is basically Usenet News with a (so some people thing -- but not 
I -- I detest so-called web-forums) purty front end to the news reader.  
Gateways between mailman, bitnet, usenet, and a bunch of other stuff existed 
for a long time (and still do, mostly).

---
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 [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Jens Alfke
>Sent: Wednesday, 9 August, 2017 13:39
>To: SQLite mailing list
>Subject: Re: [sqlite] Mailinglist question
>
>
>> On Aug 9, 2017, at 12:31 PM, Nico Williams 
>wrote:
>>
>> (It'd be great to have mostly-read-only public IMAP servers serving
>> mailing list archives.  I say mostly-read-only because one should
>want
>> to keep track of what one has read, responded to, flagged, and
>> "deleted".  But this concept appears to be a pipe dream at this
>time.)
>
>I literally spent years exploring concepts in that vein, and almost
>got a product like that built while I was at Apple ten years ago.
>
>The closest existing solution I’ve found is groups.io
>, which is a pretty good integration of mailing-
>lists and web-forums. You can get posts as emails or in a web
>interface, every email has a link to a view of the thread, and of
>course you can browse and search the archives. (Yeah, it’s a lot like
>Google Groups, but IMHO the functionality is better and the UI
>cleaner.)
>
>—Jens
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailinglist question

[Simon Slavin]

On 9 Aug 2017, at 8:10pm, Lars Frederiksen  wrote:

> Is it possible to read the mails directly on a server instead of receiving
> the mails constantly?

Sure.  See



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tutorials, books, video about SQLite

[Lars Frederiksen]

Thank you for all your advices concerning books about SQLite. I will have a 
closer look!

Lars

-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Nico Williams
Sendt: 9. august 2017 22:19
Til: SQLite mailing list
Emne: Re: [sqlite] Tutorials, books, video about SQLite

By far the best generic SQL book, IMO, is the O'Reilly "SQL" Pocket Guide.  
It's very small and yet fairly comprehensive.  It covers Oracle, DB2, SQL 
Server, and MySQL.  But it's very general and brief, and everything it has to 
teach you is generally applicable to PostgreSQL and SQLite3.

Nico
--
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tutorials, books, video about SQLite

[Nico Williams]

By far the best generic SQL book, IMO, is the O'Reilly "SQL" Pocket
Guide.  It's very small and yet fairly comprehensive.  It covers Oracle,
DB2, SQL Server, and MySQL.  But it's very general and brief, and
everything it has to teach you is generally applicable to PostgreSQL and
SQLite3.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tutorials, books, video about SQLite

[Bob Friesenhahn]

On Wed, 9 Aug 2017, Lars Frederiksen wrote:


I would appreciate very much  if you clever people out there have some
booktitles or links to tutorials (websites, video etc) about SQLite.


The O'Reilly book "Using SQLite" has been the most useful book for me 
thus far.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tutorials, books, video about SQLite

[Richard Hipp]

On 8/9/17, Lars Frederiksen  wrote:
>
> I would appreciate very much  if you clever people out there have some
> booktitles or links to tutorials (websites, video etc) about SQLite.
>

Go so the SQLite homepage https://www.sqlite.org/ and find the
"Search" option on the far right of the menu bar.  Click it.  In the
search box type in "books" and then press "Go".  Following the first
hyperlink in the search results.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tutorials, books, video about SQLite

[Don V Nielsen]

Safari Books Online. I believe there are a number of SQLite title there.

On Wed, Aug 9, 2017 at 2:06 PM, Lars Frederiksen  wrote:

> Hi
>
>
>
> I would appreciate very much  if you clever people out there have some
> booktitles or links to tutorials (websites, video etc) about SQLite.
>
>
>
> Regards
>
> Lars
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailinglist question

[Peter Da Silva]

On 8/9/17, 2:45 PM, "sqlite-users on behalf of Nico Williams" 
 wrote:
> I'm not fond of web fora, but a good integration would be nice, for sure.  An 
> IMAP service would be fantastic for users like me.  The need to keep some 
> state (unless we enhance MUAs to do it for read-only IMAP mailboxes) requires 
> user accounts, which requires more of a business model :(

Set up a Usenet server: NNTP reader mode/NNRP keeps that state in the client.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailinglist question

[Nico Williams]

On Wed, Aug 09, 2017 at 12:38:42PM -0700, Jens Alfke wrote:
> > On Aug 9, 2017, at 12:31 PM, Nico Williams  wrote:
> > (It'd be great to have mostly-read-only public IMAP servers serving
> > mailing list archives.  I say mostly-read-only because one should want
> > to keep track of what one has read, responded to, flagged, and
> > "deleted".  But this concept appears to be a pipe dream at this time.)
> 
> I literally spent years exploring concepts in that vein, and almost
> got a product like that built while I was at Apple ten years ago.

Supposedly the IETF was going to contract out setting up such a service
for IETF lists.  IDK what became of that.  I should check.

> The closest existing solution I’ve found is groups.io
> , which is a pretty good integration of
> mailing-lists and web-forums. You can get posts as emails or in a web
> interface, every email has a link to a view of the thread, and of
> course you can browse and search the archives. (Yeah, it’s a lot like
> Google Groups, but IMHO the functionality is better and the UI
> cleaner.)

I'm not fond of web fora, but a good integration would be nice, for
sure.  An IMAP service would be fantastic for users like me.  The need
to keep some state (unless we enhance MUAs to do it for read-only IMAP
mailboxes) requires user accounts, which requires more of a business
model :(

Nico
-- 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailinglist question

[Jens Alfke]

> On Aug 9, 2017, at 12:31 PM, Nico Williams  wrote:
> 
> (It'd be great to have mostly-read-only public IMAP servers serving
> mailing list archives.  I say mostly-read-only because one should want
> to keep track of what one has read, responded to, flagged, and
> "deleted".  But this concept appears to be a pipe dream at this time.)

I literally spent years exploring concepts in that vein, and almost got a 
product like that built while I was at Apple ten years ago.

The closest existing solution I’ve found is groups.io , 
which is a pretty good integration of mailing-lists and web-forums. You can get 
posts as emails or in a web interface, every email has a link to a view of the 
thread, and of course you can browse and search the archives. (Yeah, it’s a lot 
like Google Groups, but IMHO the functionality is better and the UI cleaner.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mailinglist question

[Nico Williams]

On Wed, Aug 09, 2017 at 09:10:58PM +0200, Lars Frederiksen wrote:
> Is it possible to read the mails directly on a server instead of receiving
> the mails constantly?

There are several archives online, and if you're subscribed you can use
your list subscription password to use the mailman archives.

(It'd be great to have mostly-read-only public IMAP servers serving
mailing list archives.  I say mostly-read-only because one should want
to keep track of what one has read, responded to, flagged, and
"deleted".  But this concept appears to be a pipe dream at this time.)

Nico
-- 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] calculated-value indexes are not covering?

[Nico Williams]

On Wed, Aug 09, 2017 at 06:59:18PM +, Wout Mertens wrote:
> but… index s is covering and only includes the field s? I thought a
> covering index was one where all the data needed to satisfy the query is in
> index? I would say that all the indexes here conform to that definition?

No, "covering" means that the columns listed in the index include all
the columns from the source table that you need for a given query:

  CREATE TABLE t(j TEXT, s TEXT, foo TEXT);
  SELECT s, j FROM t WHERE s = 'foo'; -- full table scan bc [s] is not
  -- indexed, is not a PRIMARY KEY,
  -- and is not UNIQUE
  CREATE INDEX t1 ON t(s);
  SELECT s FROM t WHERE s = 'foo';-- uses index; index covers column
  -- selection (just [s])

  SELECT s, j FROM t WHERE s = 'foo'; -- full table scan unless [s] is
  -- a PRIMARY KEY

  CREATE INDEX t2 ON t(j, s);
  SELECT s, j FROM t WHERE s = 'foo'; -- full table scan; t2 doesn't
  -- help because we need a covering
  -- index where [s] is a prefix

  CREATE INDEX t3 ON t(s, j);
  SELECT s, j FROM t WHERE s = 'foo'; -- uses covering index t3, finally

  SELECT s, j, foo FROM t WHERE s = 'foo'; -- t3 does not cover -> full
   -- table scan

Usually you should have a PRIMARY KEY, and if [s] were one here, then
none of these would need full table scans, but only two of these would
use only an index and not also index the table via the PK.

  -- truly covering index, but only usable in queries by [s] or [s],
  -- [j], or [s], [j], [foo]:
  CREATE INDEX t4 ON t(s, j, foo);

Nico
-- 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mailinglist question

[Lars Frederiksen]

Hi

Is it possible to read the mails directly on a server instead of receiving
the mails constantly?

Regards

Lars

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] calculated-value indexes are not covering?

[David Raymond]

There's the issue of whether SQLite takes the value from the index, or 
recalculates it from the table data. So for a "covering index" you would need 
to index all the inputs to the function, for example

sqlite> create index lc on t (length(s), s);

sqlite> explain query plan select distinct length(s) from t;
selectid|order|from|detail
0|0|0|SCAN TABLE t USING COVERING INDEX lc


If you look at the explain output you can see that the expression index is 
still opening the main table in addition to the index. I believe using the 
actual value stored in the index is either a future optimization, or only works 
for functions which are explicitly marked as deterministic. Someone else can 
provide some more light on that.


sqlite> explain select distinct length(s) from t indexed by l;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 13000  Start at 13
1 Null   1 2 008  r[2]=NULL
2 OpenRead   0 2 0 2  00  root=2 iDb=0; t
3 OpenRead   2 4 0 k(2,,) 00  root=4 iDb=0; l
4 Explain0 0 0 SCAN TABLE t USING INDEX l  00
5 Rewind 2 121 0  00
6   DeferredSeek   2 0 000  Move 0 to 2.rowid 
if needed
7   Column 2 0 100  r[1]=
8   Eq 1 11280  if r[2]==r[1] goto 
11
9   Copy   1 2 000  r[2]=r[1]
10  ResultRow  1 1 000  output=r[1]
11Next   2 6 001
12Halt   0 0 000
13Transaction0 0 4 0  01  usesStmtJournal=0
14Goto   0 1 000
Run Time: real 0.020 user 0.00 sys 0.00

sqlite> explain select distinct length(s) from t indexed by lc;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 11000  Start at 11
1 Null   1 2 008  r[2]=NULL
2 OpenRead   2 5 0 k(3,,,)00  root=5 iDb=0; lc
3 Explain0 0 0 SCAN TABLE t USING COVERING INDEX lc  00
4 Rewind 2 101 0  00
5   Column 2 0 100  r[1]=
6   Eq 1 9 280  if r[2]==r[1] goto 9
7   Copy   1 2 000  r[2]=r[1]
8   ResultRow  1 1 000  output=r[1]
9 Next   2 5 001
10Halt   0 0 000
11Transaction0 0 4 0  01  usesStmtJournal=0
12Goto   0 1 000
Run Time: real 0.019 user 0.00 sys 0.00

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Wout Mertens
Sent: Wednesday, August 09, 2017 2:59 PM
To: SQLite mailing list
Subject: Re: [sqlite] calculated-value indexes are not covering?

but… index s is covering and only includes the field s? I thought a
covering index was one where all the data needed to satisfy the query is in
index? I would say that all the indexes here conform to that definition?

https://sqlite.org/optoverview.html 8.0 covering index

> If, however, all columns that were to be fetched from the table are
already available in the index itself, SQLite will use the values contained
in the index and will never look up the original table row

On Wed, Aug 9, 2017 at 8:55 PM Nico Williams  wrote:

> On Wed, Aug 09, 2017 at 06:48:51PM +, Wout Mertens wrote:
> > sqlite> create table t(j json, s string);
> > sqlite> create index s on t(s);
> > sqlite> create index j on t(json_extract(j, '$.foo'));
> > sqlite> create index l on t(length(s));
>
> In order for any of these indices to be covering indices you need to add
> all the columns of the table t to them:
>
> sqlite> create table t(j json, s string);
> sqlite> create index s on t(s, j);
> sqlite> create index j on t(json_extract(j, '$.foo'), j, s);
> sqlite> create index l on t(length(s), s, j);
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list

[sqlite] Tutorials, books, video about SQLite

[Lars Frederiksen]

Hi 

 

I would appreciate very much  if you clever people out there have some
booktitles or links to tutorials (websites, video etc) about SQLite. 

 

Regards

Lars

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LSM1 extension

[Richard Hipp]

On 8/9/17, Jens Alfke  wrote:
>
> One thing I’m unclear on: Will the LSM1 extension in SQLite3 be able to plug
> in at a low level (replacing the b-tree engine), so one can use it with all
> the existing features like relational tables and SQL queries; or will it
> provide only a basic key/value store API?

It is just a separate key/value store using a different file.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] calculated-value indexes are not covering?

[Wout Mertens]

but… index s is covering and only includes the field s? I thought a
covering index was one where all the data needed to satisfy the query is in
index? I would say that all the indexes here conform to that definition?

https://sqlite.org/optoverview.html 8.0 covering index

> If, however, all columns that were to be fetched from the table are
already available in the index itself, SQLite will use the values contained
in the index and will never look up the original table row

On Wed, Aug 9, 2017 at 8:55 PM Nico Williams  wrote:

> On Wed, Aug 09, 2017 at 06:48:51PM +, Wout Mertens wrote:
> > sqlite> create table t(j json, s string);
> > sqlite> create index s on t(s);
> > sqlite> create index j on t(json_extract(j, '$.foo'));
> > sqlite> create index l on t(length(s));
>
> In order for any of these indices to be covering indices you need to add
> all the columns of the table t to them:
>
> sqlite> create table t(j json, s string);
> sqlite> create index s on t(s, j);
> sqlite> create index j on t(json_extract(j, '$.foo'), j, s);
> sqlite> create index l on t(length(s), s, j);
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LSM1 extension

[Jens Alfke]

> On Aug 9, 2017, at 8:23 AM, Nico Williams  wrote:
> 
> I must say though, SQLite4 is *brilliant*.  I do hope that it either
> gets completed and replaces SQLite3, or has all its good ideas folded
> into SQLite3.  The LSM1 work in SQLite3 seems to point towords the
> latter.

One thing I’m unclear on: Will the LSM1 extension in SQLite3 be able to plug in 
at a low level (replacing the b-tree engine), so one can use it with all the 
existing features like relational tables and SQL queries; or will it provide 
only a basic key/value store API?

(Sorry if it’s off-topic to discuss future features here. I know there’s a 
development-focused list but I think I’d be way beyond my depth there. LSM1 is 
potentially very, very relevant to the project I’m working on, but that depends 
on how it ends up integrated, which is why I ask.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] calculated-value indexes are not covering?

[Nico Williams]

On Wed, Aug 09, 2017 at 06:48:51PM +, Wout Mertens wrote:
> sqlite> create table t(j json, s string);
> sqlite> create index s on t(s);
> sqlite> create index j on t(json_extract(j, '$.foo'));
> sqlite> create index l on t(length(s));

In order for any of these indices to be covering indices you need to add
all the columns of the table t to them:

sqlite> create table t(j json, s string);
sqlite> create index s on t(s, j);
sqlite> create index j on t(json_extract(j, '$.foo'), j, s);
sqlite> create index l on t(length(s), s, j);

Nico
-- 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] calculated-value indexes are not covering?

[Wout Mertens]

Back with more indexing questions :)

12991 $ sqlite3
SQLite version 3.19.3 2017-06-08 14:26:16
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(j json, s string);
sqlite> create index s on t(s);
sqlite> create index j on t(json_extract(j, '$.foo'));
sqlite> create index l on t(length(s));
sqlite> explain query plan select distinct s from t;
0|0|0|SCAN TABLE t USING COVERING INDEX s
sqlite> explain query plan select distinct json_extract(j, '$.foo') from t;
0|0|0|SCAN TABLE t USING INDEX j
sqlite> explain query plan select distinct length(s) from t;
0|0|0|SCAN TABLE t USING INDEX l

How come the indexes on calculated values are not considered COVERING?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using multi-value indexes for subset queries

[Wout Mertens]

Sqlite is just really smart :) Doing a `count(*)` on my table with one
constraint of a two-valued index does a table scan and completes in 9ms,
and when I force use of the index, it's 100ms. I'll stop trying to
second-guess the query optimizer now ;)

On Wed, Aug 9, 2017 at 5:11 PM Wout Mertens  wrote:

> Indeed, in trying to reproduce on a simple table it does use the index,
> even with json_extract values. I must be doing something wrong in my app,
> thanks.
>
>
>
> On Wed, Aug 9, 2017 at 5:07 PM David Raymond 
> wrote:
>
>> There's a guideline for what sort of things SQLite will look for in an
>> index and a query. There are plenty of more advanced ways to make use of
>> indexes I'm sure, but currently those would fall into "future optimization
>> opportunities."
>> http://www.sqlite.org/optoverview.html
>>
>> In your case the index on a, b is indeed a candidate for use in the
>> query, but apparently the planner doesn't think it will be more beneficial
>> than the full scan. This is where ANALYZE can help by letting the planner
>> have more info about the indexes.
>>
>> You can always use "INDEXED BY" to force it to use a specific index, and
>> if you get an error along the lines of "Error: no query solution" then
>> you'll know it definitely can't be used, either because of a typo while
>> making it or some other reason.
>>
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Wout Mertens
>> Sent: Wednesday, August 09, 2017 10:51 AM
>> To: SQLite mailing list
>> Subject: [sqlite] Using multi-value indexes for subset queries
>>
>> Hi all,
>>
>> in experimenting with indexes I found that if you create an index on (a,
>> b)
>> and then SELECT * FROM data WHERE a = 1 AND B = 2, it will use the index,
>> great.
>>
>> However, if you write SELECT * FROM data WHERE a = 1, it won't use the
>> index. If you write SELECT * FROM data WHERE a = 1 AND B !=
>> some_impossible_value, it does use the index.
>>
>> Is it not efficient to use an index, even if one of its values is not
>> constrained?
>>
>> Case in point, I have an app that allows searching across 4 of parameters,
>> and I am wondering if I could just use a single 4-valued index, searching
>> for != impossible_val for unconstrained parameters. Surely that would be a
>> better use of resources than indexes on all combinations?
>>
>> And if that is indeed the case, would it not be good that sqlite
>> automatically checks multi-value indexes where it can't find a specific
>> index?
>>
>> Wondering Wout.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LSM1 extension

[Nico Williams]

On Wed, Aug 09, 2017 at 08:26:51AM -0500, Charles Leifer wrote:
> sqlite4's future is uncertain. It's not, as I understand, meant to replace
> sqlite3 any time soon. I think it was more of a place to try out new ideas
> for implementations.

I must say though, SQLite4 is *brilliant*.  I do hope that it either
gets completed and replaces SQLite3, or has all its good ideas folded
into SQLite3.  The LSM1 work in SQLite3 seems to point towords the
latter.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using multi-value indexes for subset queries

[Wout Mertens]

Indeed, in trying to reproduce on a simple table it does use the index,
even with json_extract values. I must be doing something wrong in my app,
thanks.



On Wed, Aug 9, 2017 at 5:07 PM David Raymond 
wrote:

> There's a guideline for what sort of things SQLite will look for in an
> index and a query. There are plenty of more advanced ways to make use of
> indexes I'm sure, but currently those would fall into "future optimization
> opportunities."
> http://www.sqlite.org/optoverview.html
>
> In your case the index on a, b is indeed a candidate for use in the query,
> but apparently the planner doesn't think it will be more beneficial than
> the full scan. This is where ANALYZE can help by letting the planner have
> more info about the indexes.
>
> You can always use "INDEXED BY" to force it to use a specific index, and
> if you get an error along the lines of "Error: no query solution" then
> you'll know it definitely can't be used, either because of a typo while
> making it or some other reason.
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Wout Mertens
> Sent: Wednesday, August 09, 2017 10:51 AM
> To: SQLite mailing list
> Subject: [sqlite] Using multi-value indexes for subset queries
>
> Hi all,
>
> in experimenting with indexes I found that if you create an index on (a, b)
> and then SELECT * FROM data WHERE a = 1 AND B = 2, it will use the index,
> great.
>
> However, if you write SELECT * FROM data WHERE a = 1, it won't use the
> index. If you write SELECT * FROM data WHERE a = 1 AND B !=
> some_impossible_value, it does use the index.
>
> Is it not efficient to use an index, even if one of its values is not
> constrained?
>
> Case in point, I have an app that allows searching across 4 of parameters,
> and I am wondering if I could just use a single 4-valued index, searching
> for != impossible_val for unconstrained parameters. Surely that would be a
> better use of resources than indexes on all combinations?
>
> And if that is indeed the case, would it not be good that sqlite
> automatically checks multi-value indexes where it can't find a specific
> index?
>
> Wondering Wout.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using multi-value indexes for subset queries

[David Raymond]

There's a guideline for what sort of things SQLite will look for in an index 
and a query. There are plenty of more advanced ways to make use of indexes I'm 
sure, but currently those would fall into "future optimization opportunities."
http://www.sqlite.org/optoverview.html

In your case the index on a, b is indeed a candidate for use in the query, but 
apparently the planner doesn't think it will be more beneficial than the full 
scan. This is where ANALYZE can help by letting the planner have more info 
about the indexes.

You can always use "INDEXED BY" to force it to use a specific index, and if you 
get an error along the lines of "Error: no query solution" then you'll know it 
definitely can't be used, either because of a typo while making it or some 
other reason.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Wout Mertens
Sent: Wednesday, August 09, 2017 10:51 AM
To: SQLite mailing list
Subject: [sqlite] Using multi-value indexes for subset queries

Hi all,

in experimenting with indexes I found that if you create an index on (a, b)
and then SELECT * FROM data WHERE a = 1 AND B = 2, it will use the index,
great.

However, if you write SELECT * FROM data WHERE a = 1, it won't use the
index. If you write SELECT * FROM data WHERE a = 1 AND B !=
some_impossible_value, it does use the index.

Is it not efficient to use an index, even if one of its values is not
constrained?

Case in point, I have an app that allows searching across 4 of parameters,
and I am wondering if I could just use a single 4-valued index, searching
for != impossible_val for unconstrained parameters. Surely that would be a
better use of resources than indexes on all combinations?

And if that is indeed the case, would it not be good that sqlite
automatically checks multi-value indexes where it can't find a specific
index?

Wondering Wout.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using multi-value indexes for subset queries

[Hick Gunter]

Your experiments are not reproducible unless you provide at least an indication 
of the schema.

Most probably, something you have not yet considered/revealed makes using the 
index to look up a irrelevant. Maybe a is declared as "integer primary key", 
making it an alias of the rowid.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Wout Mertens
Gesendet: Mittwoch, 09. August 2017 16:51
An: SQLite mailing list 
Betreff: [sqlite] Using multi-value indexes for subset queries

Hi all,

in experimenting with indexes I found that if you create an index on (a, b) and 
then SELECT * FROM data WHERE a = 1 AND B = 2, it will use the index, great.

However, if you write SELECT * FROM data WHERE a = 1, it won't use the index. 
If you write SELECT * FROM data WHERE a = 1 AND B != some_impossible_value, it 
does use the index.

Is it not efficient to use an index, even if one of its values is not 
constrained?

Case in point, I have an app that allows searching across 4 of parameters, and 
I am wondering if I could just use a single 4-valued index, searching for != 
impossible_val for unconstrained parameters. Surely that would be a better use 
of resources than indexes on all combinations?

And if that is indeed the case, would it not be good that sqlite automatically 
checks multi-value indexes where it can't find a specific index?

Wondering Wout.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using multi-value indexes for subset queries

[Igor Tandetnik]

On 8/9/2017 10:50 AM, Wout Mertens wrote:

in experimenting with indexes I found that if you create an index on (a, b)
and then SELECT * FROM data WHERE a = 1 AND B = 2, it will use the index,
great.

However, if you write SELECT * FROM data WHERE a = 1, it won't use the
index.


Are you sure? How do you determine that? My experience is different.


Case in point, I have an app that allows searching across 4 of parameters,
and I am wondering if I could just use a single 4-valued index


If you have an index on (a, b, c, d), it'll help for conditions like "a=1" or "a=1 and b=2" or "a=1 and b=2 and 
c=3" - conditions that use a prefix on an index. But it won't help with "b=2" or "c=3" or "b=2 and c=3".

Think of it this way. Imagine you have a phone book, with entries sorted by last name and 
then first name. In this book, it's easy to find people named "Smith, John", or 
all people with last name of Smith - but it won't at all help to find all people with 
first name of John.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using multi-value indexes for subset queries

[Wout Mertens]

Hi all,

in experimenting with indexes I found that if you create an index on (a, b)
and then SELECT * FROM data WHERE a = 1 AND B = 2, it will use the index,
great.

However, if you write SELECT * FROM data WHERE a = 1, it won't use the
index. If you write SELECT * FROM data WHERE a = 1 AND B !=
some_impossible_value, it does use the index.

Is it not efficient to use an index, even if one of its values is not
constrained?

Case in point, I have an app that allows searching across 4 of parameters,
and I am wondering if I could just use a single 4-valued index, searching
for != impossible_val for unconstrained parameters. Surely that would be a
better use of resources than indexes on all combinations?

And if that is indeed the case, would it not be good that sqlite
automatically checks multi-value indexes where it can't find a specific
index?

Wondering Wout.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LSM1 extension

[Charles Leifer]

sqlite4's future is uncertain. It's not, as I understand, meant to replace
sqlite3 any time soon. I think it was more of a place to try out new ideas
for implementations.

On Wed, Aug 9, 2017 at 3:10 AM, x  wrote:

> Thanks Charles. Is sqlite4 available yet?
>
> From: Charles Leifer
> Sent: 08 August 2017 21:21
> To: SQLite mailing list
> Subject: Re: [sqlite] LSM1 extension
>
> There's some information that may be of interest on the sqlite4 wiki:
>
> * Design overview: https://sqlite.org/src4/doc/trunk/www/lsm.wiki
> * User guide: https://sqlite.org/src4/doc/trunk/www/lsmusr.wiki
> * API: https://sqlite.org/src4/doc/trunk/www/lsmapi.wiki
>
> My interest in this particular feature stems from my having written a set
> of python bindings to the sqlite4 implementation (to be used as a sort of
> embedded ordered key/value storage engine). I've wondered about the
> possibility of exposing these APIs as a virtual table, and so seeing this
> extension really excited me. So obviously my interest is much greater than
> that of the average SQLite user :)
>
> On Tue, Aug 8, 2017 at 12:54 PM, x  wrote:
>
> > Thanks Richard.
> >
> > From: Richard Hipp
> > Sent: 08 August 2017 18:47
> > To: SQLite mailing list
> > Subject: Re: [sqlite] LSM1 extension
> >
> > On 8/8/17, x  wrote:
> > > I keep reading about this LSM1 but can’t find anything about it beyond
> > > tickets. Any links?
> >
> > Baseline technology: https://en.wikipedia.org/wiki/
> > Log-structured_merge-tree
> >
> > No documentation yet on the LSM1 implementation in SQLite.
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [FirDAC][Phys][SQLite]ERROR: Cipher: DB is not encrypted

[Lars Frederiksen]

Hi,

 

I have added my SQLite database to DataExplorer in Delphi 10.1.

 

I get this Errormessage

 

[FirDAC][Phys][SQLite]ERROR: Cipher: DB is not encrypted

 

When double click on some of the nodes or if I try to refresh the node of my
database. My Encrypt property in the FDConnection Editor is set to "No" but
changing this to one of the encryption possibilities does not (and should
not) change anything.

 

Any solution?

 

Regards

Lars

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] hex and char functions

[x]

Thanks Rowan. I’ve picked up bits and pieces from the various replies to get a 
basic idea of what’s going on. My question should’ve been posted on the c++ 
builder forum in the first place. Sorry for wasting everyone’s time.

Tom



From: Rowan Worth
Sent: 09 August 2017 03:29
To: SQLite mailing list
Subject: Re: [sqlite] hex and char functions

On 8 August 2017 at 18:32, x  wrote:

> Why does it always turn out to be my own fault ☹
>

Welcome to programming ;)

It's very rare in practice to find an actual bug in a library or language,
especially one as widely deployed and tested as sqlite. Glad you're sorted,
I just wanted to have a quick go at clearing this up:

but finding out that the UTF8 code for the UTF16 code \u0085 is in fact
> \uc285 has tipped me over the edge
>

1. \u0085 is a unicode code-point, 'NEXT LINE (NEL)'
2. \uc285 is a unicode code-point, 'HANGUL SYLLABLE SWIT (솅)'
3. Neither of these is a UTF8 or UTF8 "code" -- UTF8 and UTF16 are
different ways of enconding/decoding unicode code-points
4. In UTF8, \u0085 is encoded as two bytes -- 0xc2 0x85
5. In UTF8, \uc285 is encoded as three bytes -- 0xec 0x8a 0x85

HTH,
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LSM1 extension

[x]

Thanks Charles. Is sqlite4 available yet?

From: Charles Leifer
Sent: 08 August 2017 21:21
To: SQLite mailing list
Subject: Re: [sqlite] LSM1 extension

There's some information that may be of interest on the sqlite4 wiki:

* Design overview: https://sqlite.org/src4/doc/trunk/www/lsm.wiki
* User guide: https://sqlite.org/src4/doc/trunk/www/lsmusr.wiki
* API: https://sqlite.org/src4/doc/trunk/www/lsmapi.wiki

My interest in this particular feature stems from my having written a set
of python bindings to the sqlite4 implementation (to be used as a sort of
embedded ordered key/value storage engine). I've wondered about the
possibility of exposing these APIs as a virtual table, and so seeing this
extension really excited me. So obviously my interest is much greater than
that of the average SQLite user :)

On Tue, Aug 8, 2017 at 12:54 PM, x  wrote:

> Thanks Richard.
>
> From: Richard Hipp
> Sent: 08 August 2017 18:47
> To: SQLite mailing list
> Subject: Re: [sqlite] LSM1 extension
>
> On 8/8/17, x  wrote:
> > I keep reading about this LSM1 but can’t find anything about it beyond
> > tickets. Any links?
>
> Baseline technology: https://en.wikipedia.org/wiki/
> Log-structured_merge-tree
>
> No documentation yet on the LSM1 implementation in SQLite.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing searches across several indexes

[Wout Mertens]

This mailing list is so amazing :) Thank you both, everything is clear now!

On Wed, Aug 9, 2017 at 9:08 AM Hick Gunter  wrote:

> An index is only usable for that subset of a queries' equality constraints
> that forms a leading subset of the fields handled by the index.
>
> E.g.if  you are looking at equality constraints for fields a, b and c in
> one query, then you need an index whose first three fields are a, b and c
> (in any order).
>
> You are obviously creating separate indexes on each field, so these
> indexes will only speed up 1 constraint. An index on field a has no idea of
> the values of b for the rows with a given value of a, so SQLite needs to
> visit each row with that value of a. Only an index on (a,b) or (b,a) will
> allow you to find the rows having specified values for a and b without
> visiting any others.
>
> If you have a specific set of queries that your application uses (i.e. a
> set of questions that arise from the real world process you are modelling),
> you can create indexes on all the combinations of constraints, load a
> representative dataset, run ANALYZE and then retrieve the query plans for
> all of your queries, and then drop all the indices that are never used.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Wout Mertens
> Gesendet: Mittwoch, 09. August 2017 08:31
> An: SQLite mailing list 
> Betreff: [sqlite] Optimizing searches across several indexes
>
> Hi,
>
> I have a table with a bunch of data (in json). I want to search on several
> values, each one is indexed. However, if I search for COND1 AND COND2, the
> query plan is simply
>
> SEARCH TABLE data USING INDEX cond1Index (cond1=?)
>
> Is this normal? I was hoping it could use the indexes somehow for both
> conditions.
>
> Should I be creating indexes differently to cover two dimensions at once?
> Perhaps with r*tree?
>
> Wout.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing searches across several indexes

[Hick Gunter]

An index is only usable for that subset of a queries' equality constraints that 
forms a leading subset of the fields handled by the index.

E.g.if  you are looking at equality constraints for fields a, b and c in one 
query, then you need an index whose first three fields are a, b and c (in any 
order).

You are obviously creating separate indexes on each field, so these indexes 
will only speed up 1 constraint. An index on field a has no idea of the values 
of b for the rows with a given value of a, so SQLite needs to visit each row 
with that value of a. Only an index on (a,b) or (b,a) will allow you to find 
the rows having specified values for a and b without visiting any others.

If you have a specific set of queries that your application uses (i.e. a set of 
questions that arise from the real world process you are modelling), you can 
create indexes on all the combinations of constraints, load a representative 
dataset, run ANALYZE and then retrieve the query plans for all of your queries, 
and then drop all the indices that are never used.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Wout Mertens
Gesendet: Mittwoch, 09. August 2017 08:31
An: SQLite mailing list 
Betreff: [sqlite] Optimizing searches across several indexes

Hi,

I have a table with a bunch of data (in json). I want to search on several 
values, each one is indexed. However, if I search for COND1 AND COND2, the 
query plan is simply

SEARCH TABLE data USING INDEX cond1Index (cond1=?)

Is this normal? I was hoping it could use the indexes somehow for both 
conditions.

Should I be creating indexes differently to cover two dimensions at once?
Perhaps with r*tree?

Wout.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing searches across several indexes

[Clemens Ladisch]

Wout Mertens wrote:
> I have a table with a bunch of data (in json). I want to search on several
> values, each one is indexed. However, if I search for COND1 AND COND2, the
> query plan is simply
>
> SEARCH TABLE data USING INDEX cond1Index (cond1=?)
>
> Is this normal?

Yes.  A query can use only a single index per table.

> Should I be creating indexes differently to cover two dimensions at once?

Create an index on both colums:

  CREATE INDEX cond1and2Index ON data(cond1, cond2);

If you are not using equality comparisons on all but the last columns in
the index, you might need an expression index.  (With JSON, I guess you
are already doing this.)

> Perhaps with r*tree?

That would be useful mainly for multidimensional, numeric range queries
(e.g., "xColumn BETWEEN ? AND ? AND yColumn BETWEEN ? AND ?").


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Optimizing searches across several indexes

[Wout Mertens]

Hi,

I have a table with a bunch of data (in json). I want to search on several
values, each one is indexed. However, if I search for COND1 AND COND2, the
query plan is simply

SEARCH TABLE data USING INDEX cond1Index (cond1=?)

Is this normal? I was hoping it could use the indexes somehow for both
conditions.

Should I be creating indexes differently to cover two dimensions at once?
Perhaps with r*tree?

Wout.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users